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

I am almost finished changing a cursor-based stored procedure ov开发者_开发知识库er 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?

---

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.

---

This is documented - the BNF for UPDATE in BOL (extract) reads

...
UPDATE 
    ...
    SET
        { column_name = { expression | DEFAULT | NULL }
    ...

The definition for expression being:

expression

Is a variable, literal value, expression, or a subselect statement (enclosed with parentheses) that returns a single value. The value returned by expression replaces the existing value in column_name or @variable.

an SP execution is none of these.

You need to find another way to apply the logic - as JNK suggests in a comment, you may be able to convert the SP logic to a function to use it in an update.

Alternatively, could the SP be re-written (or a new SP written) to work in a set-based way on a group of records?

---

You can't call a function or stored procedure from UPDATE statement (if somebody find out in MS SQL, please let me know how), but you can definitely run UPDATE in-between your code.

So whatever it is that you are trying to achieve with your function or stored procedure, run UPDATE statement right within your function or stored procedure block.

For example: once my client required customized ID in database alongside generic ID. So I baked customized ID with data type string and ran UPDATE within baking. I am going to post example code below...

INSERT INTO ID (
F_Name
, L_Name
, [Date]
)
VALUES(
'Josie'
, 'Smith'
, getdate()
)

BEGIN
DECLARE @VAR int    -- after all other columns have been populated @VAR holds generic ID
        , @ID VARCHAR(8)    -- the INT value @VAR holds is passed down as string to @ID 
        , @R VARCHAR(8)     -- @R holds custome value, what ever you want to customise
        , @RID VARCHAR(16); -- @RID holds the string value of @ID and @R togather
SET @VAR = (SELECT MAX( ID) FROM ID)
SET @ID = @VAR
SET @R = 'RID-000'
SET @RID = @R+@ID
Update ID   -- I am running update statement right inside this block, to update custom ID column with customised ID
SET RID = @RID
WHERE ID = @VAR -- in where clause I am bounding this customised ID to populate only current ID filed
END

SELECT * FROM ID

The results are as follows:
    ID  F_Name  L_Name  Date        RID
1   218 Joe     Smith   2018-05-29  RID-000218
2   219 Jane    Smith   2018-05-29  RID-000219
3   220 Jane    Smith   2018-05-29  RID-000220
4   221 Josie   Smith   2018-05-29  RID-000221

Alternatively I could have taken entire chunk of code between BEGIN and END and put it in stored procedure and run it "right after" the INSERT statement. Just keeping this principle in mind I have used this trick as work around to needing to use stored procedure or function in UPDATE statement.

One more thing, I don't think you can customize the main ID/PK filed of the table (if you find out, you could, please let me know too). So custome ID will always be second/separate column.