November 2, 2010 at 3:04 pm
I am almost finished changing a cursor bases stored procedure over to set based. Amost, because I have only one thing left to figure out.
They use a stored procedure called GetSequence to query a table, update it with a new sequence number (old +1) and return the new sequence number value. This wasn't an issue when they used cursors because they assigned the output value to a variable, then used the variable.
The only way I can think of to keep the new stored procedure set based is to execute GetSequence in and INSERT or UPDATE statement. However, I get that wonderfully specific "Incorrect syntax near the keyword 'EXEC'" when I try that.
This is the old code:
DECLARE @new_UD_campaignID BIGINT -- Get the new ud_lead_id for the new lead set
EXEC ppGlobal.dbo.Getsequence
'ud_campaign_id',
@new_UD_campaignID OUTPUT
DECLARE @OrderNum VARCHAR(9);
IF @corpCamp LIKE '%LEP%'
BEGIN
SELECT @OrderNum = ( 'L' + RIGHT('00000000' + CAST(@new_UD_campaignID AS VARCHAR(8)), 8) )
END
ELSE
BEGIN
SELECT @OrderNum = ( 'C' + RIGHT('00000000' + CAST(@new_UD_campaignID AS VARCHAR(8)), 8) )
END
This works fine.
The new code I am trying looks like this:
UPDATE @List
SET OrderNumBigInt = EXEC (ipCore.dbo.Getsequence
'ud_campaign_id',
@new_UD_campaignID OUTPUT)
I can't find any specific documentation indicating that you cannot execute a stored procedure within a SELECT of UPDATE statement to set a column value.
Has anyone tried something similar, but with success?
Thanks!
November 2, 2010 at 3:10 pm
Nope. I have only used the return value, or selected the results of the exec into a temp table and then used whatever I needed from the #tmp.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply