收录日期:2020/12/02 23:15:24 时间:2010-09-07 20:33:05 标签:sql,sql-server,sql-server-2005

Hey all, is there any SQL Server 2005 guru that can tell me a type of trick to get the values of what is being queried?

Ex:

 UPDATE l SET Inactive = 1 
 FROM tbl1 e JOIN tbl2 l ON l.CID = e.CID
     JOIN tbl3 p ON p.PID = e.PID
     JOIN tbl4 c ON c.PID = e.PID
     LEFT JOIN tbl5 g ON g.EID = e.ID 
         AND g.PID = e.PID
 WHERE e.PID = '315' 
 AND Inactive = 0 
 AND (e.Active = 0 OR g.ID IS NULL)

How can I get the values of, say, e.CID after this executes? I've already tried

 Print e.CID

But of course that does not work. Any other way to do that?

Thanks!

David

Try the OUTPUT clause:

UPDATE l 
SET Inactive = 1 
OUTPUT e.CID
FROM tbl1 e 
  JOIN tbl2 l ON l.CID = e.CID
  JOIN tbl3 p ON p.PID = e.PID
  JOIN tbl4 c ON c.PID = e.PID
  LEFT JOIN tbl5 g ON g.EID = e.ID AND g.PID = e.PID
WHERE e.PID = '315' 
AND Inactive = 0 
AND (e.Active = 0 OR g.ID IS NULL)

That should show the e.CID column for each row being updated as a result set from your UPDATE query.