我有一个存储过程,为了这个问题,我已经大大缩减了它的规模,但本质上我需要帮助的问题是这样的。
如果表xyz中的一行被更新,我需要将ID彼此追加并输出回调用应用程序。更新工作与预期一样,问题出在我构建输出@IPV_ID_Found的方式上(参见代码底部的注释部分)。
@IPV_Status varchar (50),
@IPV_ID_Found varchar(500) = 'A' OUTPUT
IF (@IPV_Status ='closed')
BEGIN
UPDATE TEST_TBL
SET
Status = 'xyz',
WHERE
ID = @IPV_ID
-- this works for one ID
SELECT @IPV_ID_Found = (CAST(@IPV_ID AS VARCHAR(500)))
-- this does not work for multiple IDs
SELECT @IPV_ID_Found = @IPV_ID_Found + (CAST(@IPV_ID AS VARCHAR(500))) + ','
-- neither does this
SET @IPV_ID_Found = @IPV_ID_Found + (CAST(@IPV_ID AS VARCHAR(500))) + ','
SELECT @IPV_ID_Found
END发布于 2012-01-06 19:05:36
查看更改:
-- this does not work for multiple IDs
SELECT @IPV_ID_Found = @IPV_ID_Found + ',' + (CAST(@IPV_ID AS VARCHAR(500))) 然后,您将获得一个连接在一起的值列表,如1,2,3,4,5
但如果需要返回记录集,则不需要输出参数,请改用表变量:
declare @IPV_ID_Found table(Item varchar(500))
IF (@IPV_Status ='closed')
BEGIN
UPDATE TEST_TBL
SET
Status = 'xyz',
WHERE
ID = @IPV_ID
-- this works for one ID
insert @IPV_ID_Found
VALUES (CAST(@IPV_ID AS VARCHAR(500)))
-- this does not work for multiple IDs
INSERT @IPV_ID_Found
VALUES (CAST(@IPV_ID AS VARCHAR(500)))
SELECT Item FROM @IPV_ID_Found
ENDhttps://stackoverflow.com/questions/8756609
复制相似问题