SQL Server 2008 OUTPUT clause

Last Post 16 Jan 2011 07:30 AM by gunneyk. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
PeteM
New Member
New Member

--
09 Dec 2010 03:45 AM
Can anyone help me with the OUTPUT clause on SQL Server 2008?

On this page: http://msdn.microsoft.com/en-us/lib....100).aspx

It states that rows are returned to the client if the statement encounters errors.

Note An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement. I am trying to get this to work and have the following T-SQL script:

use tempdb

CREATE TABLE DeletedEmployee
(
[ID] Char(10) PRIMARY KEY NOT NULL, [Name] Varchar(50) NOT NULL
)

CREATE TABLE Employee
(
[ID] Char(10) PRIMARY KEY NOT NULL, [Name] Varchar(50) NOT NULL
)

CREATE TABLE EmployeeHolidays
(
Unq_Key INT IDENTITY(1, 1) NOT NULL,
ID Char(10) PRIMARY KEY NOT NULL REFERENCES Employee(ID), [Reason] Varchar(50) NOT NULL
)

INSERT INTO Employee(ID, [NAME]) VALUES('PM', 'Peter')
INSERT INTO EmployeeHolidays(ID, Reason) VALUES('PM', 'Christmas Holidays')

DELETE FROM Employee OUTPUT DELETED.ID, Deleted.Name INTO DeletedEmployee

-- Gives no rows
Select * FROM DeletedEmployee

Now I know why the delete does not work, because of the foreign key. but that is not the question. The question is that Microsoft says that the OUTPUT clause will return rows regardless of any errors, but I don't seem able to recreate what they are saying. What I have done seems to be OK, so does anyone have any ideas? I am using SQL Server 2008.
gunneyk
New Member
New Member

--
09 Dec 2010 04:35 AM
I believe what it was trying to say is that if any rows would have been returned before an error occurs you would get those. I don't have an example but I am pretty sure that is what they meant.
PeteM
New Member
New Member

--
09 Dec 2010 05:16 AM
Hi gunneyk

Thanks for replying. What you say is what I thought, but I don't get any rows. I know that there is a foreign key constraint, and this is the reason for the failure, but I deliberately wanted it to fail as I wanted to see if I got any rows from the OUTPUT clause. If you have any examples of replicating what Microsoft say, I would be very happy to see them!
gunneyk
New Member
New Member

--
16 Jan 2011 07:30 AM
Well since your example fails on the first row it attempts to delete there would be none to return. I interpret BOL to mean that if you were deleting 100 rows and you failed on the 50th row it would have output the first 49. But this is just a theory. I am not sure why you would want the rows if there was an error since the operation would be rolled back anyway.
You are not authorized to post a reply.

Acceptable Use Policy