UPDATE statement uses EXEC to execute a stored procedure to SET a column value

Question

I am almost finished changing a cursor-based stored procedure over to set-based. Almost, 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 an INSERT or UPDATE statement. However, I get that wonderfully specific error, "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, but is really slow because it is in a cursor and updating over two million rows.

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 or UPDATE statement to set a column value.

Has anyone tried something similar, but with success?

Answer 1

What you're suggesting can't be done in MSSQL (AFAIK). In fact, I doubt the suggestions to convert GetSequence into a function probably won't work either as the latest ud_campaing_id probably is stored in some "global" table...

Assuming the GetSequence stored procedure is called by different processes "simultaneously", I'd suggest you'd either

• need to adapt said sp so you can ask for a bunch of codes at once (extra parameter, eg. @number_of_ids which defaults to 1) so that the output parameter returns the first id requested but internally also reserves the next n ones for you which you then can use to update your @list
• need to create a tight loop that gets you the number of a id's and then apply these in one go to your target table.

Although I'm most certainly in favour of the former solution, it requires changes to what seems to be a very core stored procedure, something the dba's might not like or allow. Nevertheless, it would make things MUCH faster. The second solution still requires some looping, and also has some serious indexing-requirements when applying the resulting data to the end-table so it's far from perfect but might at least be a bit faster than looping directly over the target table and fetching and applying the new data record by record.

Judging on the UPDATE @list approach you're using I think you're already on track for the second suggestion. Assuming you have an identity field in @list (with a UNIQUE OR PK constraint on it and no gaps), you might try something along these lines :

DECLARE @RecordID, @LastRecordID int
DECLARE @new_UD_campaignID bigint

SELECT @RecordID = Min(RecordID), 
       @LastRecordID = Max(RecordID)
  FROM @list

DECLARE @newCampaingIDs TABLE (RecordID int PRIMARY KEY, new_UD_campaignID varchar(8))

WHILE @RecordID <= @LastRecordID
    BEGIN

        EXEC ppGlobal.dbo.Getsequence 'ud_campaign_id', @new_UD_campaignID OUTPUT

        INSERT @newCampaingIDs (RecordID, new_UD_campaignID) VALUES (@RecordID, RIGHT('00000000' + CAST(@new_UD_campaignID AS VARCHAR(8)), 8))

        SELECT @RecordID = @RecordID + 1
    END

UPDATE @list
   SET OrderNum = (CASE WHEN corpCamp LIKE '%LEP%' THEN 'L' ELSE 'C' END) + new_UD_campaignID
  FROM @list upd
  JOIN @newCampaingIDs new
    ON new.RecordID = upd.RecordID

The reason I think this will be faster is because the sequential inserts will have (a lot?) less overhead than updating the original table record by record. Then again, you're still stuck behind the repeatedly calling of the GetSequence stored proc which might be your major time consumer.

Anyway, the only way to know for sure is by testing it =)

Good luck.