OUTPUT clause

Question

OUTPUT clause

Ray K

SSC-Dedicated

Points: 31163
More actions
October 21, 2010 at 2:58 pm

#228330

Hi folks . . . hopefully this is just a quick question.
Was just reading BOL regarding the OUTPUT clause. I'd like to take a column value generated from an INSERT and pass it to a variable.
Does the variable have to be a TABLE variable, or is there a way to pass it into a common variable such as INT or NVARCHAR (or, in my particular situation, a UNIQUEIDENTIFIER)?
Thanks!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

You can either use a table (table variable, temporary table, permanent table) (results to into it), or not (results to client). But not to a "regular" variable. See the BOL OUTPUT Clause entry.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ray K SSC-Dedicated Points: 31163 More actions · Answer 2

Okay, that's what I figured.

I ended up just creating a simple SELECT statement for the column immediately following the INSERT. I'm just writing a quick 'n dirty, and it's insignificant enough that having to create a TABLE variable for it would be unnecessary overkill.

Thanks!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

WayneS SSC Guru Points: 95460 More actions · Answer 3

Ray K (10/21/2010)
Okay, that's what I figured.
I ended up just creating a simple SELECT statement for the column immediately following the INSERT. I'm just writing a quick 'n dirty, and it's insignificant enough that having to create a TABLE variable for it would be unnecessary overkill.
Thanks!

Say what? Overkill???

DECLARE @test-2 TABLE (Col1 uniqueidentifier);

INSERT INTO .....

OUTPUT i.MyUniqueIdentifierColumn INTO @test-2

....

SELECT * FROM @test-2;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ray K SSC-Dedicated Points: 31163 More actions · Answer 4

WayneS (10/21/2010)
Say what? Overkill???

Edit: my original reply was a bit too flippant -- didn't want anyone to take it the wrong way!

Maybe "overkill" wasn't the right word -- rather, what I was doing wasn't terribly big, and I didn't think it necessitated the need to create a table variable.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Muhammad Tariq Ten Centuries Points: 1020 More actions · Answer 5

see Examples at below link:

http://mssqlsolutions.blogspot.com/2008/08/how-to-use-output-clause.html

Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com