sql-server - 在sql中设置值

Question

如何在过程中将结果集值设置为局部变量或输出参数..

如下。

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

Answer 1

您可以定义一个表变量来保存输出:

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

然后您可以从该表变量中进行选择以从您的存储过程返回数据(或者只是查看它)。

PS:WITH (UPDLOCK) 绝对是多余的 - 任何 UPDATE 语句无论如何都会获取更新锁......你可以忽略它完全。