I have a stored proc which is doing a search from a few tables by doing an inner join. When I enter a new search string, the proc takes some time to get the results. Whereas when I run this again after a few times, it gives results immediately. I know it is due to proc cache but still is there any way to sor this out.
Another problem is that, if a enter a string which is not available in the database itself then it takes a very long time. But takes relatively less time if the string exists in the database.
Please help, I am trying to write a dynamic search stored procedure using three fields. I use the same logic on the front end using VB to SQL server which works fine, but never tried a stored procedure with the logic. Can you please help me, construct a stored procedure. User can choose any of the three(progno, projno, contractno) fields as a where condition.
I ma using asp.net as front end with sql server backend.
CREATE PROCEDURE dbo.USP_Searchrecords (@ProgNO nvarchar(50), @ProjNOnvarchar(50) , @ContractNOnvarchar(50)) AS DECLARE @myselect nvarchar(2000) DECLARE @psql nvarchar(2000) DECLARE @strsql nvarchar(2000) SET NOCOUNT ON
@psql = "SELECT * FROM Mytable"
IF @ProgNO <> '' then strsql = WHERE ProgNO = @ProgNO end if
If @ProjNO <> '' then if strsql <> '' then strsql = strsql & " and ProjNO =@ProjNO ELSE strsql = wHERE ProjNO =@ProjNO END IF END IF
If @ContractNO <> '' then if strsql <> '' then strsql = strsql & " and ContractNO =@ContractNO ELSE strsql = wHERE ContractNO =@ContractNO END IF END IF
We have modified a column name for a table. Now we need to find out all the database objects (stored procedures, functions, views) which access this table so that we can modify them. This is a very big database and its not easy to open the code for each object and check if its access that table. To add to the complexity, its not just one table and column but a number of tables have been modified.
basically, what I am trying to achieve to 2 types of search functions...
Search for All terms (easy and complete) and search for Any Terms...
the way I have gone about this so far is to in my asp.net app, split the search string by spaces, and then search for each word, and merging the resulting dataset into the main return dataset.
this, however has a few problems. the result dataset will contain duplicate values, and i am running queries in a loop.
What i am looking for is a one-stop-shop stored procedue that will split the search string, loop through each word, and add the results to a return table, ONLY if it does not exist already within the return table.
Can anyone point me in the right direction... basically with the splitting of the string and the looping through the words...the rest i think i can handle...
or any other hints/tips/tricks would also be helpful.
I developed a search stored proc that searches all orsome of Procs, Views, Triggers and functions. Would anyone be interestedto see it posted here?Do you have any suggestions about other places, websites forums ...., toshare something I have developed?Thanks you for your response in advancePLease send me emailJoin Bytes! {Remove ### before responding}
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS ( SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum FROM[UT_Pole] A LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
Dave writes "I am in the dark on this one, but maybe by talking about it I can figure something out. I am an amature with sql, so bear with me. What is need to do is search 3-4 fields in a table. For example. If I search for "I am a football fan of the Colts" I somehow need to take out those conjunctions and smaller words. Not to mention weighting the search results. Any help would save me"
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
Hello, I'm trying to rewrite the following proc for better performance. Which approach would be the best way? CREATE PROCEDURE dbo.upTest(@ProvID int, @GewID int) AS IF @ProvID IS NULL AND @GewID IS NULL SELECT ProvID, Prov, GewID FROM dbo.Alg_Geo_Prov ELSE IF NOT @ProvID IS NULL SELECT ProvID, Prov, GewID FROM dbo.Alg_Geo_Prov WHERE ProvID = @ProvID ELSE IF NOT @GewID IS NULL SELECT ProvID, Prov, GewID FROM dbo.Alg_Geo_Prov WHERE GewID = @GewID ELSE SELECT ProvID, Prov, GewID FROM dbo.Alg_Geo_Prov WHERE (ProvID = @ProvID) AND (GewID = @GewID) GO
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc) { //call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL }
spSQL (
INSERT INTO #tmpABC EXECUTE spSQL2 )
spSQL2 ( // some other t-sql stored proc )
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
I have a number of complex search stored procedures that use the following syntax to try to simplify the code.
WHERE @SearchParam IS NULL OR SearchCol = @SearchParam
unfortunately it appears that this is really inefficient as far as the database is concerned.
If I run the following query on the AdventureWorks database (SQL Server 2005 with SP2 and fixes up to v3054)
Code Block SET STATISTICS IO ON
Declare @CustomerID int SET @CustomerID = 1
SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE @CustomerID IS NULL OR CustomerID = @CustomerID
SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE CustomerID = @CustomerID
Is see that the first select results in 45 logical reads, whereas the second results in only 2 logical reads.
Does anyone have any idea how I can get the benefit of a search procedure that does not have loads of IF blocks, or dynamic SQL without this major performance issue?
I am facing some performance issues in a Stored Procedure. The procedure needs to return a resultset based on some search criteria. There are around 20 possible search criteria. Below is the SQL query used in my Stored procedure. Any help to optimize the search will be great:
--get LOV details in table variables INSERT INTO @tblLov (LovCode, LovDesc, ParamCode) SELECT LovCode, LovDesc, ParamCode FROM tp_Lov WITH (NOLOCK) WHERE ParamCode IN('FileSrc', 'CommTrailInd', 'CommTxnStatus', 'AgencyPrincipalInd','ProdSubType','AuditTransStatus')
--get commission transaction according to the search criteria INSERT INTO @tblSearchResults SELECT l1.LovDesc AS TransSource, l2.LOVDesc AS CommTrailInd, r.RemitCode as RemitNumber, t.IntTransId as TransNumber, CONVERT(VARCHAR, t.TrdDt, 110) AS TradeDate, CONVERT(VARCHAR, t.SettlementDt, 110) AS SettlementDate, rp.RepCode, (ISNULL(rp.LstNm,'') + ', ' + ISNULL(rp.FstNm,'')) AS RepName, (CASE WHEN ISNULL(t.IntClntId,0)=0 THEN ISNULL(t.ClntShortNM, '') + (CASE WHEN (t.TransSrc = 'NSM' OR (t.TransSrc = 'MCE' AND ISNULL(t.ProdType,'') <> 'VA')) AND ISNULL(t.FundAcctNum,'')<>'' THEN ' - ' + ISNULL(t.FundAcctNum,'') WHEN (t.TransSrc = 'NSV' OR (t.TransSrc = 'MCE' AND ISNULL(t.ProdType,'') = 'VA')) AND ISNULL(t.PolicyNum,'')<>'' THEN ' - ' + ISNULL(t.PolicyNum,'')
WHEN t.TransSrc IN('PSH','MSR') AND ISNULL(t.ClrHouseAcctNum,'')<>'' THEN ' - ' + ISNULL(t.ClrHouseAcctNum,'') ELSE '' END) ELSE dev.udf_COMM_PCD_GetClientName(t.IntClntId, t.IntTransId) END) AS Client, (CASE WHEN ISNULL(t.CUSIP,'')='' THEN t.ProdName ELSE p.ProdNm END) AS [Product], t.InvAmt AS InvestmentAmt, t.GDC AS GDC, t.ClrChrg AS ClearingCharge, t.NetComm AS NetCommission, (CASE WHEN t.Status IN(@strLov_TxnStatus_Tobepaid, @strLov_TxnStatus_Paid) THEN dev.udf_COMM_PCD_GetPayoutRateString(t.IntTransId) ELSE '' END) AS PayoutRate, (CASE WHEN t.Status IN(@strLov_TxnStatus_Tobepaid, @strLov_TxnStatus_Paid) THEN dev.udf_COMM_PCD_GetPayoutAmountString(t.IntTransId) ELSE '' END) AS Payout, l3.LOVDesc AS TransStatus, t.Comments, t.OrderMarkup AS BDMarkup, t.IntTransId, rp.IntRepId, sch.SchCode, t.IntClntId, t.CUSIP, t.RepIdValue AS RepAlias, t.RepIdType, t.SplitInd, l4.LOVDesc AS AgencyPrincipalInd, t.AgencyPrincipalFee, t.EmployeeTradeInd, t.ShareMarkup, t.UnitsTraded, s.SponsorNm, CASE WHEN t.TransSrc = 'NSM' OR (t.TransSrc = 'MCE' AND ISNULL(t.ProdType,'') <> 'VA') THEN ISNULL(t.FundAcctNum,'') --Production Defect #873 & 877 WHEN t.TransSrc = 'NSV' OR (t.TransSrc = 'MCE' AND ISNULL(t.ProdType,'') = 'VA') THEN ISNULL(t.PolicyNum,'') ELSE t.ClrHouseAcctNum END, CASE WHEN ISNULL(t.ProdSubType,'') IN ('', 'Z') THEN 'Not Defined' ELSE l6.LovDesc END AS ProdSubType, --t.ProdSubType, l5.LOVDesc AS TransAuditStatus, --t.TransAuditStatus, t.TransAuditStatus AS TransAuditStatusCode, t.OriginalTransId, t.RowId, t.Status, t.intParentTransId, t.CancelTrdInd, t.ClrChrgOverrideInd, 9999 AS AuditKey FROM tr_CommTrans t WITH (NOLOCK) INNER JOIN @tblLov l1 ON t.TransSrc = l1.LOVCode and l1.ParamCode = 'FileSrc' INNER JOIN @tblLov l2 ON t.CommTrailInd = l2.LOVCode and l2.ParamCode = 'CommTrailInd' INNER JOIN @tblLov l3 ON t.Status = l3.LOVCode and l3.ParamCode = 'CommTxnStatus' INNER JOIN td_Remit r WITH (NOLOCK) ON t.IntRemitId = r.IntRemitId LEFT OUTER JOIN @tblLov l4 ON t.AgencyPrincipalInd = l4.LOVCode and l4.ParamCode = 'AgencyPrincipalInd' LEFT OUTER JOIN @tblLov l5 ON t.TransAuditStatus = l5.LOVCode AND l5.ParamCode = 'AuditTransStatus' LEFT OUTER JOIN @tblLov l6 ON t.ProdSubType = l6.LOVCode AND l6.ParamCode = 'ProdSubType' LEFT OUTER JOIN tm_BDProd p WITH (NOLOCK) ON t.CUSIP = p.CUSIP LEFT OUTER JOIN tm_BDSponsors s WITH (NOLOCK) ON t.IntBDSponsorId = s.IntBDSponsorId LEFT OUTER JOIN tm_Reps rp WITH (NOLOCK) ON t.IntRepId = rp.IntRepId LEFT OUTER JOIN tm_PayoutSch sch WITH (NOLOCK) ON t.IntSchId = sch.IntSchId WHERE t.IntTransId = (CASE WHEN @intTransId IS NULL THEN t.intTransId ELSE @intTransId END) AND t.TransSrc = @strTransSrc AND r.RemitCode = (CASE WHEN ISNULL(@strRemitCode,'')='' THEN r.RemitCode ELSE @strRemitCode END) AND ISNULL(t.SettlementDt,'01-01-1900') BETWEEN @dtmFromSettlementDt AND @dtmToSettlementDt AND ISNULL(t.TrdDt,'01-01-1900') BETWEEN @dtmFromTradeDt AND @dtmToTradeDt AND t.CommTrailInd = (CASE WHEN @chrShowTrails='Y' THEN t.CommTrailInd ELSE 'C' END) AND t.Status = (CASE WHEN ISNULL(@strStatus,'')='' THEN t.Status ELSE @strStatus END) AND ISNULL(t.ClrHouseAcctNum,'') LIKE (CASE WHEN ISNULL(@strAccountId,'')='' THEN ISNULL(t.ClrHouseAcctNum,'') WHEN (@strTransSrc = 'PSH' OR @strTransSrc = 'MSR' OR @strTransSrc = 'MSA') THEN @strAccountId ELSE ISNULL(t.ClrHouseAcctNum,'') END) AND ISNULL(t.FundAcctNum,'') LIKE (CASE WHEN ISNULL(@strAccountId,'')='' THEN ISNULL(t.FundAcctNum,'') WHEN @strTransSrc = 'NSM' THEN @strAccountId WHEN @strTransSrc = 'MCE' AND ISNULL(t.ProdType,'')<>'VA' THEN @strAccountId ELSE ISNULL(t.FundAcctNum,'') END) AND ISNULL(t.PolicyNum,'') LIKE (CASE WHEN ISNULL(@strAccountId,'')='' THEN ISNULL(t.PolicyNum,'') WHEN @strTransSrc = 'NSV' THEN @strAccountId WHEN @strTransSrc = 'MCE' AND ISNULL(t.ProdType,'')='VA' THEN @strAccountId ELSE ISNULL(t.PolicyNum,'') END) AND ISNULL(t.IntBDSponsorId,-1) = (CASE WHEN @intSponsorId IS NULL THEN ISNULL(t.IntBDSponsorId,-1) ELSE @intSponsorId END) AND ISNULL(t.ProdType,'') = (CASE WHEN ISNULL(@strProdType,'')='' THEN ISNULL(t.ProdType,'') ELSE @strProdType END) AND ISNULL(t.ProdSubType,'') = (CASE WHEN ISNULL(@strProdSubType,'') ='' THEN ISNULL(t.ProdSubType,'') ELSE @strProdSubType END) AND ISNULL(t.CUSIP,'') = (CASE WHEN ISNULL(@strCUSIP,'')='' THEN ISNULL(t.CUSIP,'') ELSE @strCUSIP END) AND ISNULL(rp.SSN, 0) = (CASE WHEN @numRepSSN IS NULL THEN ISNULL(rp.SSN, 0) ELSE @numRepSSN END) AND ISNULL(rp.RepCode,'') = (CASE WHEN ISNULL(@strRepCode,'')='' THEN ISNULL(rp.RepCode,'') ELSE @strRepCode END) AND ISNULL(rp.LstNm, '') = (CASE WHEN ISNULL(@strRepLstNm,'')='' THEN ISNULL(rp.LstNm,'') ELSE @strRepLstNm END) AND ISNULL(rp.FstNm, '') = (CASE WHEN ISNULL(@strRepFstNm,'')='' THEN ISNULL(rp.FstNm,'') ELSE @strRepFstNm END) AND ISNULL(rp.RepStatus,'') <> (CASE WHEN @chrIncludeTerminated='Y' THEN 'Z' ELSE 'T' END) AND ISNULL(t.IntClntId,-1) = (CASE WHEN @intClientId IS NULL THEN ISNULL(t.IntClntId,-1) ELSE @intClientId END) AND ( (@chrAuditReportFlag = 'N' AND t.Status NOT IN(@strLov_TxnStatus_Loaded, @strLov_TxnStatus_Cancelled) AND ISNULL(TransAuditStatus,@strLov_TransAuditStatus_Active) = @strLov_TransAuditStatus_Active ) OR (@chrAuditReportFlag = 'Y' AND t.Status NOT IN(@strLov_TxnStatus_Loaded) DefectID# 880,895
I am about to heavily index a table and have to include atleast 3 to 4 olumns in the fulltext index for this table. The table is updated very frequently and the also the columns that are involve in the fulltext indexing undergo frequent updates. As of now, I can't avoid using full text indexing as these columns are very very lengthy and basically contail text. The users of the database will give some key words as the search criteria to get infomation as to what they are looking for. How frequently should I update my full text catalog. This is a scenario where the full text is operating on various tables and each of thses table might be containingaround 300,000 to 800,00 rows. I would appreciate an intelligent siggestion as I need it as soon as possible.
I have a table with 3M rows that contains a varchar(2000) field withvarious keywords. Here is the table structure:PKColumnImageIDFullTextColumnThere is an association table:ImageIDContractIDNow, I want to do a query where the ContractID = x and Contains someword in the FullTextColumn. There is an association table that mapsImages to Contracts - so I can't use the trick of putting the Contractcode in the FullTextColumn.I'm finding that first the FTS service is performing a search on theKeyword (which can take a long time if 100K rows are returned) thenjoining to the association table for the particular contract.Is there anyway to make this faster by telling the FTS service, onlysearch this subset of rows for the keyword based on the contract.Sorry if this sounds convoluted. Appreciate any help you can suggest.Thanks!
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
Does anyone know how to how to performance case-insensitive search onXML data type in SQLServer 2005? Or I have to convert all the xml datato lower case before I store it?Thanks in advance.John
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Hi Peeps I have a SP that returns xml I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName But it seems that I cant call the proc from within a select. I have also tried declare @v xml set @v = exec sp_that_returns_xml ( @a, @b) But this again doesn't work I have tried changing the statements syntax i.e. brackets and no brackets etc..., The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table - Which to be frank is god awful way to do it. Any and all help appreciated. Kal
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
Im trying to perform an update with a stored procedure thats all working but an exception is thrown ....violation of primary key contraint....cannot insert duplicate pri key I understand whats going on but how do you update some ones details if its protected this way. ?
I have a stored procedure , where i want to return identity column after insert but before insert i want to check if the record exist then select its identity value to return , after select statement it is retutrning null CREATE PROCEDURE SP_Attendance1 (@DIVISIONID int,@EMPLOYEEID int,@CALLDATE datetime,@RECEIVEDDATE datetime,@DOC datetime,@SYSTEMNAME VARCHAR(20),@DELETED int,@attendanceID int output) AS---DECLARE @ID intIF EXISTS (SELECT ID FROM TBLATTENDANCE WHERE EMPLOYEEID = @EMPLOYEEID AND YEAR ( CALLDATE ) = YEAR ( @CALLDATE)AND MONTH (CALLDATE) = MONTH ( @CALLDATE) AND DAY (CALLDATE) = DAY ( @CALLDATE) )BEGIN SET @attendanceID = SCOPE_IDENTITY()END ELSE BEGIN INSERT INTO TBLATTENDANCE (DIVISIONID ,EMPLOYEEID ,CALLDATE , RECEIVEDDATE ,DOC,SYSTEMNAME ,DELETED )VALUES (@DIVISIONID ,@EMPLOYEEID ,@CALLDATE , @RECEIVEDDATE ,@DOC,@SYSTEMNAME ,@DELETED ) ;SELECT DIVISIONID, EMPLOYEEID, CALLDATE, RECEIVEDDATE, DOC, SYSTEMNAME, DELETED,ID FROM TBLATTENDANCE WHERE (ID = SCOPE_IDENTITY())SELECT @attendanceID = SCOPE_IDENTITY()ENDGO Kindly help with this
I am trying to create a stored proc, that delivers a recordset, per the user requirements BUT,
I want to create a Geneirc search Proc that can handle a few criteria
I was wondering if it is possible to create a VB like Select case depending on Information supplied to the stored proc
i.e @Loc_Thing @Loc_OtherThing
SELECT FirstName,LastName,CIty,Job,,Company,Webpage FROM RECORDSET WHERE Select case @Loc_Thing Case "Mickey" LastName = @Loc_OtherThing Case "Walt" Company = @Loc_OtherThing
...... etc
is it possible to create a strored proc like this? I have found a Select case in SQL, but it doesn't work I would like it? Any Idea's?
