April 5, 2002 at 8:20 am
Hi,
I am new to T-SQL and have a problem with an update stored procedure.
I want to update a record and in a Change Note I want to include all affected Workorders.
I query another table for workorders
associated with a Profile concatenate them into a variable and then try to
update the record with it.
The following stored procedure runs fine in debug mode (all variables populated) in Query analyzer,
it exits with @RETURN_VALUE = 0, but the update does not happen.
When I paste result of 'print @sqls' into QA window it updates just fine.
Any idea what I am doing wrong?
Thanks for your help.
Jakub
here is the code:
CREATE PROCEDURE [sProfLogActiveSDGs]
(@LogID_1 [int],
@Profile_3 [int],
@Note_10 [varchar](8000))
AS
DECLARE @A_SDG varchar(50)
DECLARE @SDGs varchar(500)
DECLARE @sqls varchar(500)
DECLARE ActiveSDG_Cursor CURSOR FOR
SELECT WONo
FROM PELDMS.dbo.ImportLog1
WHERE Profile = @Profile_3
SET @SDGs = ''
OPEN ActiveSDG_Cursor
FETCH NEXT FROM ActiveSDG_Cursor
INTO @A_SDG
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SDGs = @SDGs + @A_SDG + ', '
FETCH NEXT FROM ActiveSDG_Cursor
INTO @A_SDG
END
CLOSE ActiveSDG_Cursor
DEALLOCATE ActiveSDG_Cursor
SET @SDGs = @Note_10 + '----' + @SDGs
SET @sqls ='UPDATE PELDMS.dbo.tProjectLog SET Note = ''' + @Note_10 + '''
WHERE (LogID = ' + CAST(@LogID_1 AS varchar(15)) + ')'
print @sqls
EXEC (@SQLs)
GO
April 5, 2002 at 9:33 am
Maybe your privledges on the table when the Dynamic SQL is executed. Try this (No dynamic SQL), hope I followed you right.
CREATE PROCEDURE [sProfLogActiveSDGs]
(
@LogID_1 [int],
@Profile_3 [int],
@Note_10 [varchar](8000)
)
AS
SET NOCOUNT ON
SET @Note_10 = @Note_10 + '----'
SELECT @Note_10 = @Note_10 + WONo + ', ' FROM PELDMS.dbo.ImportLog1 WHERE Profile = @Profile_3
UPDATE PELDMS.dbo.tProjectLog SET Note = @Note_10 WHERE (LogID = @LogID_1)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 5, 2002 at 10:29 am
I am not sure that I follow your Select statement below:
SET @Note_10 = @Note_10 + '----'
SELECT @Note_10 = @Note_10 + WONo + ', ' FROM PELDMS.dbo.ImportLog1 WHERE Profile = @Profile_3
What I am trying to do is to enumerate WONo (there will be 1-50 different ones for one Profile) from ImportLog1 table and insert them as a text into a Note field in the tProjectlog table (with some text like 'The following workorders were affected by your change:')
I think my permissions are OK on the tables.
April 5, 2002 at 11:13 am
Why you are using 'EXEC (@SQLs)', why can't you update directly with your Update statement?
Your statement will look like:
UPDATE PELDMS.dbo.tProjectLog
SET Note = @Note_10
WHERE LogID = CAST(@LogID_1 AS varchar(15))
Are you sure that your LogID is Varchar?
April 5, 2002 at 11:50 am
This will give you the exact same output
-----------------------------------------------------
SET @Note_10 = @Note_10 + '----'
SELECT @Note_10 = @Note_10 + WONo + ', ' FROM PELDMS.dbo.ImportLog1 WHERE Profile = @Profile_3
----------------------------------------------------------------------------------------------
As this
----------------------------------------------------------------------------------------------
DECLARE ActiveSDG_Cursor CURSOR FOR
SELECT WONo
FROM PELDMS.dbo.ImportLog1
WHERE Profile = @Profile_3
SET @SDGs = ''
OPEN ActiveSDG_Cursor
FETCH NEXT FROM ActiveSDG_Cursor
INTO @A_SDG
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SDGs = @SDGs + @A_SDG + ', '
FETCH NEXT FROM ActiveSDG_Cursor
INTO @A_SDG
END
CLOSE ActiveSDG_Cursor
DEALLOCATE ActiveSDG_Cursor
SET @SDGs = @Note_10 + '----' + @SDGs
----------------------------------------------------------------------------------------------
This completes the same thing without the cursor and can eliminate some of the extra variables.
Basically as each value enters the cache buffer they are concatinated to the end just like your cursor
is doing until either the variable is full or the end is reached.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 5, 2002 at 1:24 pm
quote:
Why you are using 'EXEC (@SQLs)', why can't you update directly with your Update statement?Are you sure that your LogID is Varchar?
I was using Update originally, this is jus a last iteration of the code. That way I could use the print statement to check the SQL sysntax.
LogID is an Integer.
April 5, 2002 at 1:27 pm
quote:
This will give you the exact same output-----------------------------------------------------
SET @Note_10 = @Note_10 + '----'
SELECT @Note_10 = @Note_10 + WONo + ', ' FROM PELDMS.dbo.ImportLog1 WHERE Profile = @Profile_3
Pretty slick, I have beeen here for just couple of hours and I am learning, cool.
Thanks.
April 5, 2002 at 1:32 pm
If LogID is an Integer then you don't have to convert the original value of @LogID_1 to varchar, just use 'WHERE LogID = @LogID_1'
And update directly!
[/quote]
LogID is an Integer.
[/quote]
April 5, 2002 at 1:47 pm
Antares686,
I tried your slick code but it fails on the SELECT statement with:
Server: Msg 245, Level 16, State 1, Procedure sProfLogActiveSDGs, Line 13
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value ', ' to a column of data type int.
April 5, 2002 at 1:53 pm
This should fix.
SELECT @Note_10 = @Note_10 + CAST(WONo AS VARCHAR(5)) + ', ' FROM PELDMS.dbo.ImportLog1 WHERE Profile = @Profile_3
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 5, 2002 at 1:54 pm
I fixed if. Just a conversion:
SELECT @Note_10 = @Note_10 + CAST(WONo AS varchar(15)) + ', ' FROM PELDMS.dbo.ImportLog1 WHERE Profile = @Profile_3
BUT still no update
Debugger stops on the UPDATE statement with
@RETURN_VALUE = 0
???
April 5, 2002 at 2:43 pm
Okay how are you going about what you are doing. Also have you tried free running thre procedure and watching the server from Profiler to see if anything odd is happening?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 8, 2002 at 8:25 am
Duh??? It runs fine today. I have no idea why. I run the Maint. Plan on the server over the Weekend, that's the only change.
Thanks for all your help 🙂
April 8, 2002 at 8:47 am
Who knows why sometimes things do this. Great to hear it's working.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply