Hi All I am using SQL server 2005. I have wriiten the Stored Procedure. Once I modified anything in the Stored Procedure I use ALTER command and save it. But I am unable to save the comments with this ALTER command which I write in the beginnign of the Stored Procedure. I have given the example below. Also please tell me without using ALTER is there any way to save the stored procedure. For Ex: /************************************************************************************************************************************************************************************* * Procedure : pr_Generate_PaymentApproval_List * * Called By : PPComponent.PPPayment.GetPaymentApprovalList * Description : This proc is use to retrieve payment approval info */ ALTER PROCEDURE dbo.pr_Generate_PaymentApproval_List ( @ins_id BIGINT, @disti_id BIGINT, @geo_cd NCHAR(4) ) -- -- Regards Abdul
I am having an issue when trying to pull data from Oracle into a sqlSERVER TABLE. I'm running into an issue because I am using to much temporary space in Oracle because the return set is so large. New to sqlserver, but in Oracle the solution would be to commmit the insert after so many records have been retrieved.
Here's the procedure I wrote I would think I need to add a cursor and a counter which be used to do a commit after so many rows have been retrieved. DOes so one have a procedure I could model mine after?
ALTER PROCEDURE [dbo].[SP_Load_BO]
-- Add the parameters for the stored procedure here
Hi... Am working with asp.net with vb for a MusicProject.. In this I have playlist for particular User..ie User Selects Some Songs and clicks on AddToMyPlayList Button.... Here He can insert those songs into a new playlist or update the earlier.. the StroedProcedure is as follws: 'N' means NewPlayList and 'E' means Existing *************************************************************************************** CREATE PROCEDURE MUSIC_ADD_PLAYLIST ( @PLAYLIST_NAME VARCHAR(255), @USER_ID VARCHAR(255), @ItemList NVARCHAR(4000), @delimiter CHAR(1), @FOLDERNAME VARCHAR(255) ,@PLAYLISTTYPE CHAR(1)) AS SET NOCOUNT ON DECLARE @IDENT INT IF @PLAYLISTTYPE = 'N' BEGIN INSERT INTO MUSIC_PLAYLIST ( MUSIC_PLAYLIST_NAME, MUSIC_PLAYLIST_USER ) VALUES ( @PLAYLIST_NAME, @USER_ID ) SELECT @IDENT=@@IDENTITY FROM MUSIC_PLAYLIST END IF @PLAYLISTTYPE='E' BEGIN SELECT @IDENT=MUSIC_PLAYLIST_ID FROM MUSIC_PLAYLIST WHERE MUSIC_PLAYLIST_USER=@USER_ID AND MUSIC_PLAYLIST_NAME=@PLAYLIST_NAME END DECLARE @tempItemList NVARCHAR(4000) SET @tempItemList = @ItemList DECLARE @i INT DECLARE @Item NVARCHAR(4000) SET @tempItemList = REPLACE (@tempItemList, ' ', '') SET @i = CHARINDEX(@delimiter, @tempItemList) WHILE (LEN(@tempItemList) > 0) BEGIN IF @i = 0 SET @Item = @tempItemList ELSE SET @Item = LEFT(@tempItemList, @i - 1) -- INSERT INTO @IDTable(Item) VALUES(@Item) INSERT INTO MUSIC_SONGSLIST (Music_PlayList_Id,Music_SongName,Music_Song_Location) VALUES (@IDENT,@ITEM,@FOLDERNAME+''+@ITEM) IF @i = 0 SET @tempItemList = '' ELSE SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i) SET @i = CHARINDEX(@delimiter, @tempItemList) END GO *************************************************************************************** Here the problem is am not getting the exact result means if i add 6 songs to a playlist it is adding only 4 songs to that particular playlist.. Please help me out Thanks in Advance, Madhavi
Hi,I have stored procedure (MS SQL Server 2000) which operateson around 600 000 rows (SELECT, UPDATE, INSERT)and executes in 5 minutes,when I put it in SQL transaction it slows down to more than 5 hours (!!)I have to admit that it is not problem with data locks (beside thatprocedurenothing else is executed on db),It is not also problem with that exact procedure, other proceduresalso slow down heavily when wrapped by SQL transactionvery very seldom stored procedure within transaction executescomparably long that its copy without transactionI guess it could be MS SQL Server 2000 configuration/tuning problem.Any ideas ?Chris
The following is NOT filling the dataset or rather, 0 rows returned.....The sp.....CREATE PROCEDURE dbo.Comms @email NVARCHAR ,@num INT OUTPUT ,@userEmail NVARCHAR OUTPUT ASBEGIN DECLARE @errCode INT SELECT fldNum, fldUserEmailFROM tblCommsWHERE fldUserEmail = @email SET @errCode = 0 RETURN @errCode HANDLE_APPERR: SET @errCode = 1 RETURN @errCodeENDGOAnd the code to connect to the sp - some parameters have been removed for easier read..... Dim errCode As Integer Dim conn = dbconn.GetConnection() Dim sqlAdpt As New SqlDataAdapter Dim DS As New DataSet Dim command As SqlCommand = New SqlCommand("Comms", conn) command.Parameters.Add("@email", Trim(sEmail)) command.CommandType = CommandType.StoredProcedure command.Connection = conn sqlAdpt.SelectCommand = command Dim pNum As SqlParameter = command.Parameters.Add("@num", SqlDbType.Int) pNum.Direction = ParameterDirection.Output Dim pUserEmail As SqlParameter = command.Parameters.Add("@userEmail", SqlDbType.NVarChar) pUserEmail.Size = 256 pUserEmail.Direction = ParameterDirection.Output sqlAdpt.Fill(DS) Return DS Like I said, a lot of parameters have been removed for easier read.And it is not filling the dataset or rather I get a count of 1 back and that's not right.I am binding the DS to a datagrid this way.... Dim DScomm As New DataSet DScomm = getPts.getBabComm(sEmail) dgBabComm.DataSource = DScomm.Tables(0) And tried to count the rows DScomm.Tables(0).Rows.Count and it = 0Suggestions?Thanks all,Zath
I have a table with information about a mobile account in one table, a table for mobile plans, and a table for planfeatures. Each mobile account is associated with a planid, and may be associated with any combination of the features associated with that plan. The plan features are stored in a bridgetable which contains 'invoicedate' (date stamped on the invoice in question), 'subaccountnumber' (the cell phone number), 'planid', and 'featureid'. I've tested the UpdateMobileFeature sp directly from the SQL Profiler--it works fine on it's own. I use a stored procedure to fill the mobile table (called from aspx page), and since I use three of the four columns written above for both the mobilesub and the mobilefeature tables, I tried just adding the parameter which holds the featureid's to the sp to update the mobilesub table. Then I call the UpdateMobileFeature sp from the updatemobile sp. (The code in mobilesub that is not calling UpdateMobileDetail works well). All seems to work fine--nothing crashes or anything, but nothing is being added to the mobilefeature table. Here is the code:CREATE procedure usp_updatemobile( @InvoiceDate smalldatetime, @SubaccountNumber varchar(50), @PlanId int, @FeatureList varchar(500) --added for UpdateMobileFeatures. In the form of a comma-seperated list, to imitate an array. --***irrelevant parameters removed***--) as exec dbo.UpdateMobileFeature '@InvoiceDate','@SubaccountNumber','@PlanId','@FeatureList' --the apparently nonfunctional call if exists ( //select for the mobile row in question ) Begin //update row End Else Begin //insert new row End GOCREATE PROC dbo.UpdateMobileFeatures( @InvoiceDate smalldatetime, @SubaccountNumber varchar(50), @PlanID int, @FeatureList varchar(500))ASBEGIN SET NOCOUNT ON CREATE TABLE #TempList ( InvoiceDate smalldatetime, SubaccountNumber varchar(50), PlanID int, FeatureID int ) DECLARE @FeatureID varchar(10), @Pos int SET @FeatureList = LTRIM(RTRIM(@FeatureList))+ ',' SET @Pos = CHARINDEX(',', @FeatureList, 1) IF REPLACE(@FeatureList, ',', '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @FeatureID = LTRIM(RTRIM(LEFT(@FeatureList, @Pos - 1))) IF @FeatureID <> '' BEGIN INSERT INTO #TempList (InvoiceDate, SubaccountNumber, PlanID, FeatureID) VALUES (@InvoiceDate, @SubaccountNumber, @PlanID, CAST(@FeatureID AS int)) --Use Appropriate conversion END SET @FeatureList = RIGHT(@FeatureList, LEN(@FeatureList) - @Pos) SET @Pos = CHARINDEX(',', @FeatureList, 1) END END --SELECT o.FeatureID, CustomerID, EmployeeID, FeatureDate --FROM dbo.Features AS o -- JOIN -- #TempList t -- ON o.FeatureID = t.FeatureID
Insert Into MobileFeatures Select InvoiceDate, SubaccountNumber, PlanID, FeatureID From #TempList ENDGOHere is the method I used to call the first sp: (also with irrelevant stuff removed)public void saveCurrentMobile() { calculateTotals(); InvoiceDataSet dataset = InvoiceDataSet.GetInstance(); SqlConnection conn = (SqlConnection)Session["connection"]; SqlCommand cmdUpdateMobile; cmdUpdateMobile = new SqlCommand("usp_updatemobile", conn); cmdUpdateMobile.CommandType = CommandType.StoredProcedure; SqlParameter invoicedate = cmdUpdateMobile.Parameters.Add("@InvoiceDate", SqlDbType.SmallDateTime); invoicedate.Value = DateTime.Parse(Request.Params["InvoiceDate"].ToString()); SqlParameter cellnumber = cmdUpdateMobile.Parameters.Add("@SubaccountNumber", SqlDbType.VarChar, 50); cellnumber.Value = txtCellNumber.Text.Trim(); SqlParameter plan = cmdUpdateMobile.Parameters.Add("@PlanId", SqlDbType.Int); plan.Value = planid; SqlParameter featurelist = cmdUpdateMobile.Parameters.Add("@FeatureList", SqlDbType.VarChar, 500); featurelist.Value = planform.FeatureList;
//Response.Write(isthirdparty.ToString()); Response.Write(thirdpartycompany); SqlParameter cycle = cmdUpdateMobile.Parameters.Add("@Cycle", SqlDbType.Int); cycle.Value = Convert.ToInt32(Request.Params["Cycle"]); int returnvalue = runStoredProcedure(cmdUpdateMobile); //the sp is called within this method.
I need to write a sp to fill a date field, if another field in another table is true. need the date to reflect todays date(the date the field was marked true). I know this is an easy one but I am over thinking it. please help.
i explain what i mean is if i have stored procedure with 7 conditions for evry day in the week one condition like this i have 7 code blocks for Sunday,Monday , .....Saturday
Code Block CREATE PROC YourProc @StartDate datetime = NULL, @EndDate datetime = NULL AS SET @StartDate = COALESCE(@StartDate,DATEADD(d,DATEDIFF(d,0,GETDATE()),0))--defaulting to todays date if not supplied SET @EndDate=COALESCE(@EndDate,DATEADD(m,1,@StartDate))--defaults to 1 month from today
WHILE @StartDate <= @EndDate BEGIN SELECT CASE WHEN DATENAME( dw,@StartDate)='Sunday' AND
empid IN (SELECT empid FROM v_un WHERE (shift =51 ) THEN 1 WHEN DATENAME( dw,@StartDate)='Monday' AND empid IN (SELECT empid FROM v_un WHERE (shift =11 ) THEN 2 ............ WHEN DATENAME( dw,@StartDate)='Saturday' AND ... THEN .. END SET @StartDate=DATEADD(d,1,@StartDate) END GO
I have a stored Proc, which takes 30 min for execution. If I execute it twice it runs properly but when i do the same the third time, it gets stuck.. any reasoning for this.. I am using Insert into statements a lot here...
ALTER PROCEDURE AddUserData (@login varchar (8), @password varchar (8), @fullName varchar (50), @RoleID Int, @statusID Int) AS
if exists (SELECT login, @errorReturn FROM tbEmployee WHERE login = @login) return 55555 Else INSERT INTO tbEmployee (login, password, fullname, RoleID, statusId) VALUES (@login, @password, @fullName, @RoleID, @statusID)
RETURN @@error
the function that calls this stored procedure is this
Public Shared Function InsertUserData(ByVal login As String, ByVal password As String, ByVal fullName As String, ByVal StatusID As Integer, ByVal RoleID As Integer)
' Create the connection object Dim connection As New SqlConnection(connectionString) ' Create and initilise the command object Dim command As New SqlCommand("AddUserData", connection) command.CommandType = CommandType.StoredProcedure command.Parameters.Add("@password", SqlDbType.VarChar) command.Parameters("@password").Value = password command.Parameters.Add("@login", SqlDbType.VarChar) command.Parameters("@login").Value = login command.Parameters.Add("@fullName", SqlDbType.VarChar) command.Parameters("@fullName").Value = fullName command.Parameters.Add("@roleID", SqlDbType.Int) command.Parameters("@roleID").Value = RoleID command.Parameters.Add("@statusID", SqlDbType.Int) command.Parameters("@statusID").Value = StatusID ' Open the connection Try
connection.Open() command.ExecuteNonQuery()
Finally connection.Close() End Try
End Function
now I'm trying to get ahold of the return value from the stored procedure so I can output a message to the user in a label control.
e.g the soted procedure checks to see if a user already exists if it does it returns 55555. how can I get ahold of this 55555 and place it in a variable in the function so I can then send it to another error function to display the approperate lable message ??
Does anyone know of an internal procedure to return the timestamp of the last time a storedprocedure or table was modified? Currently the object only lists the creation timestamp.
I want find that the ntext column data string have more than 2000 characters. I need to truncate those string to segment with 200 character, then put those segments along with their table_name and column_name to another table. Maybe need to use cursor? If so, how to use it?
I have a stored procedure that basically recieves the where clause of a select statement and executes the new sql statement... ie:
CREATE PROCEDURE [dbo].[bsa_GetImportIDs] (@FilterText varchar(1000)) AS
DECLARE @MySQL varchar(1000)
SET @MySQL = "SELECT Import_ID FROM tblImport WHERE " + @FilterText
EXEC (@MySQL) GO
Now, in another stored procedure, I need to use the stored procedure above in a cursor so that I can execute an insert statement for each occurance of the Import_ID that appears in that dataset... ie:
CREATE PROCEDURE [dbo].[bsa_PutLargeCase] AS
DECLARE @CaseID uniqueidentifier SET @CaseID = NewID() Declare @ImportID uniqueidentifier
Declare curClient Cursor FAST_FORWARD for SELECT Import_ID FROM dbo.bsa_GetImportIDs (@FilterText) <---- this does not work!!!
Open curClient FETCH NEXT FROM curClient INTO @ImportID WHILE @@FETCH_STATUS = 0 BEGIN
EXEC dbo.bsa_PutCaseDetail @CaseID, @ImportID
FETCH NEXT FROM curClient INTO @ImportID END
CLOSE curClient DEALLOCATE curClient
GO
How can I utilize my first stored procedure in the cursor of the second? ... or Are there any other approaches that may be a better solution to what I am trying to accomplish?
any of you have an idea how i can declare an output parameter for my cursor which is inside a stored procedure. i would lik to see the output using the exec command but i don't know how to get the out from my cursor. please help!
Why need to open CURSOR in the part in which a procedure is created?
The following codes are listed in the Microsoft SQL Server 2005 BOOKS ONLINE.
(1)
USE AdventureWorks; GO IF OBJECT_ID ( 'dbo.currency_cursor', 'P' ) IS NOT NULL DROP PROCEDURE dbo.currency_cursor; GO CREATE PROCEDURE dbo.currency_cursor @currency_cursor CURSOR VARYING OUTPUT AS SET @currency_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT CurrencyCode, Name FROM Sales.Currency; OPEN @currency_cursor;
GO
(2)
USE AdventureWorks; GO DECLARE @MyCursor CURSOR; EXEC dbo.currency_cursor @currency_cursor = @MyCursor OUTPUT; WHILE (@@FETCH_STATUS = 0) BEGIN; FETCH NEXT FROM @MyCursor; END; CLOSE @MyCursor; DEALLOCATE @MyCursor; GO
can someone tell me how to use static cursor to read the rows applying it in procedure. i have input and output tables i will have to use input table to read data and then in procedure update/insert into output table ) insert values from input and dditioanl calulate charges. ok, there is my problem: An Internet service provider has three different subscription packages for its customers:
Package A: For $15 per month with 50 hours of access provided. Additional hours are $2.00 per hour over 50 hours. Assume usage is recorded in one-hour increments, i.e., a 25-minute session is recorded as one hour.
Package B: For $20 per month with 100 hours of access provided. Additional hours are $1.50 per hour over 100 hours.
Package C: For $25 per month with 150 hours access is provided. Additional hours are $1.00 per hour over 150 hours
Assume a 30-day billing cycle.
1) Create a table to hold customer input billing data.
2) Populate input table with follwing records:
CustomerID Pkg Hours ---------- --- ------ 1000 A 49 1010 A 50 1020 a 90 1030 a 130
1090 B 40 1100 B 99 1110 b 100 1120 b 145
1140 C 45 1150 c 85 1160 c 149 1170 c 150 1180 c 200
3) Create a table to hold customer data used to generate the statement to be sent to the customer. It should include CustomerID, Package, HoursUsed, and Charges.
Write an SQL script that reads customer billing data, calculates a customer’s monthly charges, and populates the customer statement table. Use Cursor to process records and Stored Procedures for ProcessBill and calcCharges.
insert into custinput values (1000,'A',49); insert into custinput values (1010,'A',50); insert into custinput values (1020,'a',90); insert into custinput values (1030,'a',130); insert into custinput values (1090,'B',40); insert into custinput values (1100,'B',99); insert into custinput values (1110,'b',100); insert into custinput values (1120,'b',145);
insert into custinput values (1140,'C',45); insert into custinput values (1150,'c',85);
insert into custinput values (1160,'c',149); insert into custinput values (1170,'c',150); insert into custinput values (1180,'c',200);
then there is conditions:
if upper (@pkg)= 'A' begin if @hrs<= 50 set @charges =15 else set @charges =15 + (@hrs-50)*2
end;
else if upper(@pkg)= 'B' begin if @hrs <= 100 set @charges = 20 else set @charges = 20 + (@hrs - 100)*1.5
end; else
if @hrs <=150 set @charges = 25 else set @charges =25+(@hrs-150)
insert into custoutput values(@cust_id,@pkg,@hrs,@charges)
Hi guys!!I am trying to fill a cursor with the results of a StoredProcedured, but SQL give me an syntax error message , does any one cangive me some helpI am using SQL Server, this is the first lines of my codeDECLARE FRates_Cursor CURSOR FORexec GET_FJRs_Eng 'all'OPEN FRates_Cursorif I run just the exec GET_FJRs_Eng 'all' line it give me the dataresults I am trying to put into the cursor, what that means is thestored is working fineThanks in advance
Hello:I want find that the ntext column data string have more than 2000 characters. I need to truncate those string to the segments with 200 character, then put those segments along with their table_name and column_name to another table. Maybe need to use cursor? If so, how to use it?
HI, WHILE DECLARING A CURSOR TO SELECT RECORDS FROM A TABLE WE NORMALLY WRITE :-
DECLARE CUR_NAME CURSOR FOR SELECT * FROM CLEANCUSTOMER
BUT SAY, IF I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :-
CREATE PROCEDURE MY_PROC AS SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC FROM CLEANCUSTOMER A INNER JOIN TRCUSTOMERPREFERENCE03JULY B ON A.INTCUSTOMERID = B.INTCUSTOMERID INNER JOIN TMPREFERENCE C ON B.INTPREFERENCEID = C.INTPREFERENCEID ORDER BY B.INTPREFERENCEID
WHICH IS RUNNING FINE AND GIVING ME THE REQUIRED DATA WHILE EXECUTING THE PROCEDURE :-
EXEC MY_PROC
BUT IF I WANT TO CALL THIS PROCEDURE MY_PROC WHILE DECLARING A CURSOR :-
I AM USING :-
DECLARE CHK_CUR CURSOR FOR SELECT * FROM MY_PROC
WHICH IS GIVING AN ERROR "Invalid object name 'MY_PROC'."
AND IF I USE :-
DECLARE CHK_CUR CURSOR FOR EXEC MY_PROC
WHICH IS GIVING AN ERROR "Incorrect syntax near the keyword 'EXEC'".
AND IF I USE :-
DECLARE CHK_CUR CURSOR FOR CALL MY_PROC
WHICH IS GIVING AN ERROR "Incorrect syntax near 'CALL'. "
IS THERE ANY WAY BY WHICH I CAN FETCH RECORDS FROM THE STORED PROCEDURE? HOW DO I DECLARE THE PROCEDURE WHILE WRITING THE CURSOR PLS HELP.
I NEED THIS URGENTLY, I HAVE TO USE THE CURSOR TO FETCH THE RECORDS FROM THE SP,THAT'S HOW THEY WANT IT.I CAN'T HELP IT AND I DON'T KNOW HOW
I need to write a stored procedure using T-SQL to declare a cursor for containing id(staff_no), names and specialism of all doctors that have specialism, The contents of the cursor then are to be displayed using a loop and print statement to give a formatted display of the output of each record within the cursor.
The doctors table has the following columns with specialism allowing NULL values
doctor ( staff_no CHAR(3), doctor_name CHAR(12), position CHAR(15), specialism CHAR(15), PRIMARY KEY(staff_no) )
I am trying to decalare the cursor in the below stored procedure. Can any one please help me to correct the cursor declaration?? Basically, i am testing how to declare the cursor in stored procedure.
CREATE PROCEDURE STP_EMPSAL @empno int, @Employee_Cursor CURSOR VARYING OUTPUT FOR SELECT empno FROM AdventureworksDW.dbo.emp AS OPEN Employee_Cursor; FETCH NEXT FROM Employee_Cursor into @empno; WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRAN UPDATE emp set sal= sal+ 2000 where empno = @empno and comm is null mgr='Scott'; FETCH NEXT FROM Employee_Cursor into @empno; COMMIT; END; CLOSE Employee_Cursor; DEALLOCATE Employee_Cursor;
i m new in sql...and i have this procedure..which have cursor inside..
1. i want to get all distinct date into #tempt table. 2. In the loop for each distinct date fetch all the date into another #temp 3. get max date from that #temp and use that date to get the data from original table
but i m getting 0 rows else all 8000 rows..which is wrong..can anyone help me plz...
create procedure procdate1 (@name varchar(50)) as begin SET NOCOUNT ON
DECLARE @MaxDate datetime DECLARE @Date datetime
select id, title, dated, CONVERT(CHAR(10), dated,101) as date, CONVERT(CHAR(8), dated,114) as time from general
where name = @name AND dated = @MaxDate
DECLARE CUR1 CURSOR FOR
SELECT @DATE FROM #tempt
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @Date
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT dated INTO #Date1 FROM general WHERE CONVERT(CHAR(10),dated,101) = @Date SELECT @MaxDate = MAX(dated) FROM #date1
--DROP TABLE #Date1
FETCH NEXT FROM CUR1 INTO @Date continue CLOSE CUR1 DEALLOCATE CUR1 end -- DROP TABLE #tempt end
Hi guys!i want to create one cursor in the t-sql. the problem is i want to usestored procedure instead of select command in cursor.can anyone tell me how can i use stored procedure's o/p to createcursor?i'm using sql 2000 and .net 2.0thanks,Lucky
I have stored procedure which contains follwing part of it. it says syntax when i worte line to get @@identity valuewhen delete that line command succesful. but i need to get @@identity from the insert statement and assign it to a variable and use it after any body pls tell me how to get this within a stored prosedure or what is the error of the following code bit. (#tblSalesOrders is a temporary table which containsset of records from orginal table )DECLARE @soNo1 INT DECLARE @CursorOrders CURSOR SET @CursorOrders = CURSOR FAST_FORWARD FOR select fldSoNo from #tblSalesOrders declare @newSONO1 int OPEN @CursorOrders FETCH NEXT FROM @CursorOrders INTO @soNo1 WHILE @@FETCH_STATUS = 0 BEGIN ----for each salesorder insert to salesorderline insert into tblSalesOrders (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) select (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) from #tblSalesOrders where fldSoNo =@soNo1;
set @newSONO1=SCOPE_IDENTITY; -------in this section again create another cursor for another set of records and insert to a table passing identity value return from the above insert -------------------------- SELECT @intErrorCode = @@ERRORIF (@intErrorCode <> 0) GOTO PROBLEM FETCH NEXT FROM @CursorOrders INTO @soNo1 END CLOSE @CursorOrders DEALLOCATE @CursorOrders
I have created the following stored procedure to get the text from one table and compare with them with another table and the one's that match will assign the corresponding ID. But the problem is that it only assigns the last id in the table from the main table which new_cur2 holds. So the problem is that its not updating with the correct ID its just updating with the last ID the cursor holds. Does any one know what it could be.....I think it may just be a little coding error....thanks
CREATE PROCEDURE [MYSP] AS
Declare @pdesc nvarchar(30) Declare @ssc int Declare @myid int Declare @name nvarchar(30)
Declare new_cur CURSOR DYNAMIC FOR SELECT ProductDescription, SubSubCatID FROM C2000HPB FOR UPDATE
Open new_cur FETCH FROM new_cur INTO @pdesc, @ssc While @@FETCH_STATUS = 0
BEGIN Declare new_cur2 CURSOR DYNAMIC FOR SELECT SubSubCatID, SubSubCategory FROM SSC FOR READ ONLY
Open new_cur2 FETCH FROM new_cur2 INTO @myid, @name While @@FETCH_STATUS = 0
BEGIN IF PATINDEX ('@name%',@pdesc) = 0 Set @ssc = @myid UPDATE C2000HPB SET SubSubCatID = @ssc FETCH NEXT FROM new_cur2 INTO @myid, @name
END
Close new_cur2 DEALLOCATE new_Cur2 FETCH NEXT FROM new_cur INTO @pdesc,@ssc END Close new_cur DEALLOCATE new_Cur
Can someone post some code that shows a Stored Procedure receiving a cursor that it can process - lets say a group of order detail records are received that must be saved along with the single Order header record.
And, in another example, a SP returns a result set to the calling program. - For example, a particular sale receipt is pulled up on the screen and the order detail is needed.
