proc can return table?

Question

Post reply

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 7 (of 7 total)

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

Grant Fritchey SSC Guru Points: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jeff Moden SSC Guru Points: 1000539 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)
Intro to Tally Tables and Functions

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: 397721 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jeff Moden SSC Guru Points: 1000539 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)
Intro to Tally Tables and Functions

WayneS SSC Guru Points: 95405 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?"