proc can return table?

Question

proc can return table?

repent_kog_is_near

SSCertifiable

Points: 7278
More actions
March 31, 2011 at 12:02 pm

#96193

can proc return back table of data (like function)- can you provide a small example?

Viewing 8 posts - 1 through 8 (of 8 total)

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

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

You can use a table valued parameter as an output parameter. But what are you trying to do?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

repent_kog_is_near (3/31/2011)
can proc return back table of data (like function)- can you provide a small example?

If you pre-create the target table you can use INSERT/EXEC. You can also use OPENROWSET although that makes it a bit difficult to pass parameters to do it.

Why can't you just use an Inline Table Valued Function (which can be thought of as a "parameterized view") for this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

Grant Fritchey (3/31/2011)
You can use a table valued parameter as an output parameter. But what are you trying to do?

I believe that is only available in SQL 2008 and later versions, and table parameters can only be used as input read-only parameters for stored procedures.

Table-Valued Parameters (Database Engine)

http://msdn.microsoft.com/en-us/library/bb510489(v=SQL.100).aspx

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 4

Michael Valentine Jones (3/31/2011)
Grant Fritchey (3/31/2011)
You can use a table valued parameter as an output parameter. But what are you trying to do?
I believe that is only available in SQL 2008 and later versions, and table parameters can only be used as input read-only parameters for stored procedures.
Table-Valued Parameters (Database Engine)
http://msdn.microsoft.com/en-us/library/bb510489(v=SQL.100).aspx

Oops are we on the 2005 forum again? I lose track of that so easily.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

Grant Fritchey (3/31/2011)
Michael Valentine Jones (3/31/2011)
Grant Fritchey (3/31/2011)
You can use a table valued parameter as an output parameter. But what are you trying to do?
I believe that is only available in SQL 2008 and later versions, and table parameters can only be used as input read-only parameters for stored procedures.
Table-Valued Parameters (Database Engine)
http://msdn.microsoft.com/en-us/library/bb510489(v=SQL.100).aspx
Oops are we on the 2005 forum again? I lose track of that so easily.

BWAA-HAA!!!... You could always do what Celko preaches... use only ANSI SQL and then it wouldn't matter. 😛

Just kidding!!! I'd rather see a misplaced good suggestion than that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95461 More actions · Answer 6

repent_kog_is_near (3/31/2011)
can proc return back table of data (like function)- can you provide a small example?

I'm not sure if I'm misreading this question, or the others are. But what I'm seeing here is asking if a stored procedure can return a result set - possible filtered by a parameter. If I'm reading this correctly, then sure. It would be done like this:

CREATE PROCEDURE MyTest (MyValue int)

AS

SELECT TOP (@MyValue) number

FROM master.dbo.spt_values

WHERE type = 'P'

AND number > 0

GO

and you would run this by:

EXECUTE MyTest 500; -- or whatever number you want to use

Is this what you were trying to ask?

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

Jeff Moden (3/31/2011)
Grant Fritchey (3/31/2011)
Michael Valentine Jones (3/31/2011)
Grant Fritchey (3/31/2011)
You can use a table valued parameter as an output parameter. But what are you trying to do?
I believe that is only available in SQL 2008 and later versions, and table parameters can only be used as input read-only parameters for stored procedures.
Table-Valued Parameters (Database Engine)
http://msdn.microsoft.com/en-us/library/bb510489(v=SQL.100).aspx
Oops are we on the 2005 forum again? I lose track of that so easily.
BWAA-HAA!!!... You could always do what Celko preaches... use only ANSI SQL and then it wouldn't matter. 😛
Just kidding!!! I'd rather see a misplaced good suggestion than that.

Do we need an ANSI SQL ONLY forum?

"The other developers made fun of me when I said we shouldn't use IDENTITY or GETDATE(). What can I do to make them see the true path to SQL purity?"