Can I use a store procedure output in an update statement in SQL Server?

Question

Hi all,

I'm converting some data in SQL Server 2005. I have a table update like this:

update Invoices set Invoices.InvoiceReference = 'NewRef'
where Invoices.InvoiceReference='Unknown'

But what I'd like to plug in instead of 'NewRef' is the output from a stored procedure that uses parameters from the columns of the Invoices table. The stored procedure itself does updates to another table. Is it possible? Something like this below (which is wrong of course :)

DECLARE @Ref nvarchar(20) 
update Invoices set Invoices.InvoiceReference = (
    EXEC InvoiceGenerateRef
        @ClientCode = Invoices.ClientCode,
        @EventCode = Invoices.EventCode,
        @Ref = @Ref OUTPUT
    SELECT @Ref)
where Invoices.InvoiceReference='Unknown'

Do I need to use a cursor or is the syntax just wrong?

Thanks, Chris.

Answer 1

You are almost there, the correct way to achieve what you are looking to do would be to define an output parameter as part of your stored procedure definition.

This paramter can then be used as part of your update statement.

DECLARE @Ref nvarchar(20)  

EXEC InvoiceGenerateRef
        @ClientCode = N'ABC2',
        @EventCode = N'X1'
        @Ref = @Ref OUTPUT

update Invoices 
    set Invoices.InvoiceReference = @Ref
where Invoices.InvoiceReference='Unknown'

Answer 2

I think you would be better off changing your stored procedure into either a function or a view (depending on what you actually do in the proc).

I think what you are after is to join to the resultset of a stored proc which would not work.

Answer 3

by using OPENROWSET you can query your stored procedure results just like a view:

http://blogs.technet.com/wardpond/archive/2005/08/01/the-openrowset-trick-accessing-stored-procedure-output-in-a-select-statement.aspx

so for your case this might be useful.

Answer 4

1) You could change InvoiceGenerateRef so that it could optionally save the generated Ref into InvoiceReference field. Presumably you would also have to provide parameters to define the selection

2) You could us e cursor to step round each row in

SELECT ...
FROM Invoices 
WHERE Invoices.InvoiceReference='Unknown'

and pass the details to InvoiceGenerateRef and then update the row. This is bad IMHO and will be slow (Your best bet is a set-based solution)

3) You could select the appropriate Invoices.ID's into a temporary table which would be in scope for the InvoiceGenerateRef so that it could iterate that (i.e. the choice of WHICH rows to update is external to the SProc, but the SProc does the actual updating)

CREATE TABLE #TEMP
(
    T_ID int NOT NULL
)

INSERT INTO #TEMP (T_ID)
SELECT ID
FROM Invoices 
WHERE Invoices.InvoiceReference='Unknown'

EXEC InvoiceGenerateRef @ACTION='UpdateFromTemporaryTable'

4) You could change InvoiceGenerateRef to a function that performed the same task:

UPDATE U
SET U.InvoiceReference = 
    dbo.MyInvoiceGenerateRefFunction(U.ClientCode, U.EventCode)
FROM Invoices AS U
WHERE U.InvoiceReference='Unknown'

MyInvoiceGenerateRefFunction would have to be deterministic (I think!)

This would be my preferred choice