CODEWITHSUNDEEP
sql-server
Abrar Ahmad
Abrar Ahmad

Reputation: 31

Set Value in sql

How can I set resultset value to local variable or output parameter in procedure..

For below.

UPDATE TOP (1) certificate WITH (ROWLOCK,READPAST,UPDLOCK) 
SET issued = 1 
OUTPUT INSERTED.certid, INSERTED.certiname
WHERE  issued = 0 AND year = 2011

Upvotes: 3

Views: 509

Answers (3)

Abrar Ahmad
Abrar Ahmad

Reputation: 31

DECLARE @certid INT
DECLARE @certinname VARCHAR(MAX) -- or whatever
UPDATE TOP (1) certificate WITH (ROWLOCK,READPAST) 
SET issued = 1, @certid = certid, @certinname = certinname
WHERE  issued = 0 AND year = 2011
SELECT @certid, @certinname

Upvotes: 0

RickNZ
RickNZ

Reputation: 18652

Since you're just updating a single row, try this:

DECLARE @certid INT
DECLARE @certinname VARCHAR(MAX) -- or whatever
UPDATE TOP (1) certificate WITH (ROWLOCK,READPAST) 
SET issued = 1, @certid = certid, @certinname = certinname
WHERE  issued = 0 AND year = 2011
SELECT @certid, @certinname

Upvotes: 2

marc_s
marc_s

Reputation: 755531

You can define a table variable to hold the output:

DECLARE @OutputTable TABLE (CertID INT, CertiName VARCHAR(100))  -- adapt as needed

UPDATE TOP (1) certificate WITH (ROWLOCK,READPAST,UPDLOCK) 
SET issued = 1 
OUTPUT INSERTED.certid, INSERTED.certiname INTO @OutputTable
WHERE  issued = 0 AND year = 2011 

SELECT * FROM @OutputTable

and you can then select from that table variable to return the data from your stored procedure (or just to peek into it).

PS: the WITH (UPDLOCK) is absolutely superfluous - any UPDATE statement will always take an update lock anyway...... you can just leave that out entirely.

Upvotes: 2

Related Questions