Table Variable in Stored Procedure

Question

Table Variable in Stored Procedure

PJ_SQL

SSCertifiable

Points: 6703
More actions
July 29, 2015 at 3:19 pm

#302956

What is the purpose of creating table variable and inserting data into it and selecting from table variable inside of stored proc?
Why can't the stored proc just have select statement instead of creating table variable?

Viewing 4 posts - 1 through 3 (of 3 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

PJ_SQL (7/29/2015)
What is the purpose of creating table variable and inserting data into it and selecting from table variable inside of stored proc?
Why can't the stored proc just have select statement instead of creating table variable?

If all that you are doing is selecting into the Table Variable (TV), and then promptly selecting out, then you are absolutely right. It would be better to just select the data directly, and the stored procedure does not require using the TV.

A TV (or temp table) is frequently used in a procedure to break up the code into simpler, smaller routines. They are especially useful when you might have to call use those results multiple times in the procedure.

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

drew.allen SSC Guru Points: 76988 More actions · Answer 2

Another reason is that you might be storing information from an OUTPUT clause to use later, especially if you want to use information from both the INSERTED and DELETED temporary tables.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

PJ_SQL (7/29/2015)
What is the purpose of creating table variable and inserting data into it and selecting from table variable inside of stored proc?
Why can't the stored proc just have select statement instead of creating table variable?

Most of the times I have seen this at clients it was because of developers who had no idea what they were doing. One client in fact had this pattern in about 90% of there over 2000 stored procedures!! Quite silly, but it made me look pretty good when they removed them at my recommendation. 😎

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service