Table variable as Output Parameter

Question

Table variable as Output Parameter

aziz.kapadia

Old Hand

Points: 395
More actions
December 31, 2008 at 2:58 am

#97029

Can I declare table variable as output parameter in stored procedure?

Viewing 15 posts - 1 through 15 (of 18 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

No not in a stored proc in SQL 2005. You can use XML variable instead or use a table valued function if possible in your case.

"Keep Trying"

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 2

Another option might be to work with temporary table, but that depends on what you are trying to do and how you are doing it.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

aziz.kapadia Old Hand Points: 395 More actions · Answer 3

I want to take output as a table and want to use it in other stored procedure.

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

I think you can do that in 2008, not that it helps you in 2005.

"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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

Grant Fritchey (12/31/2008)
I think you can do that in 2008, not that it helps you in 2005.

Table valued parameters exist in SQL Server 2008, but it can only be input read only parameters. Since the original poster wanted an output parameter, he won't be able to use the table valued parameters even with SQL Server 2008

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

Thanks for the correction. I wasn't sure and I haven't really looked at the table valued parameters yet. I should have kept my mouth shut.

"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

Seggerman-675349 Hall of Fame Points: 3563 More actions · Answer 7

Check out table functions as an option, depending of course on what you are trying to do.

dhaval.samaranayeke SSC Enthusiast Points: 122 More actions · Answer 8

If you want to use a table for a stored procedure, you can create a function which will return a table and then use this function in your stored procedure. Something like this

CREATE FUNCTION dbo.fnFunction_Name

(

@Param1 VARCHAR(8000),

@Param2 VARCHAR(8000)

)

RETURNS @Results TABLE (intRowId INTEGER IDENTITY(1,1) , Items VARCHAR(8000))

AS

BEGIN

//Your body here.

//You can insert the resultset in the table variable @Results in the body.

//At the end do not forget to return the variable

Return

END

You can use this function directly as a table i.e.

SELECT * FROM dbo.fnFunction_Name(@Param1, @Param2)

I hope this will be of some help to you.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

If your stored proc doesn't lend itself to being re-written as a function, you can always look at using a self-referenced linked server set up. Meaning - set up the DB instance as a linked server to itself, so that you can use OPENQUERY (which would allow you to use the recordset from a stored proc in the same places as a table).

As in - the following would work (assuming the stored proc has a SELECT statement outputtting data)

select * from OPENQUERY(mySelfLink, 'Exec myDb.dbo.MyStoredProc @sqlParams') q

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

You could just return your table data from your proc as SELECT output and then the caller could catch it by using the INSERT..EXEC.. statement.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 11

Grant Fritchey (12/31/2008)
Thanks for the correction. I wasn't sure and I haven't really looked at the table valued parameters yet. I should have kept my mouth shut.

Heh... nope... then I wouldn't have learned what I just learned. 😛

--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)

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 12

aziz.kapadia (12/31/2008)
Can I declare table variable as output parameter in stored procedure?

Probably of more importance, what is it that you're trying to do? You'll be amazed at the possible solutions you'll get if you describe the original problem rather than a possible solution. 😉

--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)

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 13

Jeff Moden (1/2/2009)
aziz.kapadia (12/31/2008)
Can I declare table variable as output parameter in stored procedure?
Probably of more importance, what is it that you're trying to do? You'll be amazed at the possible solutions you'll get if you describe the original problem rather than a possible solution. 😉

Heh... guess ya must of solved your problem, huh? 😉

--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)

ichiyo85 Old Hand Points: 306 More actions · Answer 14

Hi, on a similar note, I want to make a stored procedure where I can delete customers from a December customer table, but only those who will show up again in January. Keep those who are not returning, and insert the existing and new customers from January. In other word, I am trying to update a customer table every month by deleting the old and replace/insert the new.

Something like:

delete from AccountMaster

where AccountMaster.AccountID in (select AccountID from RevDec2010)

insert into AccountMaster (AccountID,Period)

select distinct AccountID, Period

from RevJan2011

How can I declare the table object "RevDec2010" and so forth in the stored procedure?

Thank you very very much in advance!!