SQL Server存储过程参数输出

我有一个存储过程，为了这个问题的目的，我已经大大缩小了它的规模，但本质上我需要帮助的问题是这个。

如果表中的一行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

查看更改：

 -- 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

BUT如果需要返回记录集，则不需要 OUTPUT 参数，而是使用表变量：

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                  
END