The Ins and Outs of OUTPUT

Are you familiar with the OUTPUT clause in SQL Server? It can be used with commands like INSERT, UPDATE, DELETE, and MERGE. It can also be used with setting variables in stored procedures. Using the tried and true StackOverflow2013, we’ll narrow it down today to focus on how INSERT/DELETE are typically used for logging table changes as well as an example of how to use OUTPUT with stored procedures.

Get In

What if we need to do some work with the PostTypes table? For this example, let’s start with a temp table to track any new inserts:

CREATE TABLE #TestInsert (
	InsertID INT,  
	InsertType NVARCHAR(50)
);

We’ll insert a new PostTypes record and OUTPUT to #TestInsert:

INSERT INTO PostTypes
OUTPUT INSERTED.Id, INSERTED.Type
INTO #TestInsert 
VALUES ('CustomType1');

Later on in our work we’ll insert another:

INSERT INTO PostTypes
OUTPUT INSERTED.Id, INSERTED.Type
INTO #TestInsert 
VALUES ('CustomType2');

If we get to the end of our work and want to see what’s been inserted we can check our temp #TestInsert table:

SELECT InsertID, InsertType
FROM #TestInsert ;
GO

We can also see these records in the PostTypes table:

SELECT Id, Type
FROM PostTypes;
GO

Get Out

What if we want to use OUTPUT to track deletions? The process is similar. We’ll create a table to hold our deletions:

CREATE TABLE #TestDel (
	DeleteID INT,  
	DeleteType NVARCHAR(50)
);

And delete the records we just added:

DELETE FROM PostTypes
OUTPUT deleted.Id, deleted.Type
INTO #TestDel 
WHERE Id > 8;

We can see our deleted records were saved to #TestDel:

SELECT DeleteID, DeleteType
FROM #TestDel;
GO

And we can confirm our records were removed from PostTypes:

SELECT Id, Type
FROM PostTypes;
GO

OUTPUT With Stored Procedure

Let’s say we want to retrieve information on Users based on location and we want to use the user count for additional work. We can create a stored procedure and specify “@UserCount INT” but also add OUTPUT. After our SELECT within the stored procedure, we’ll set @UserCount with the amount of rows the SELECT returned:

CREATE PROCEDURE [dbo].[GetUserCountByLocation] (
	@UserLocation AS NVARCHAR(100)
	,@UserCount INT OUTPUT
	)
AS
BEGIN
	SELECT Id
		,Displayname
		,Location
	FROM Users
	WHERE Location = @UserLocation

	SET @UserCount = @@ROWCOUNT;
END
GO

When we execute the stored procedure, we can include @CountFromProc to store our row count after the stored procedure completes:

DECLARE @CountFromProc AS INT;

EXEC [GetUserCountByLocation] @UserLocation = 'Ohio'
	,@UserCount = @CountFromProc OUTPUT;

SELECT @CountFromProc AS 'CountFromProc';

Meetup for More

We stepped through some examples of how OUTPUT can be used with INSERT, DELETE, and stored procedures. As timing would have it, Steve Stedman has a Meetup to discuss OUTPUT on November 9. Check that out if you’re interested to learn more.

Thanks for reading!