Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Table variable as Output Parameter Expand / Collapse
Author
Message
Posted Wednesday, December 31, 2008 2:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, March 11, 2012 11:11 AM
Points: 31, Visits: 29
Can I declare table variable as output parameter in stored procedure?
Post #627916
Posted Wednesday, December 31, 2008 3:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
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"
Post #627924
Posted Wednesday, December 31, 2008 4:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:43 PM
Points: 2,109, Visits: 5,419
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/
Post #627947
Posted Wednesday, December 31, 2008 4:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, March 11, 2012 11:11 AM
Points: 31, Visits: 29
I want to take output as a table and want to use it in other stored procedure.
Post #627950
Posted Wednesday, December 31, 2008 6:03 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 15,661, Visits: 28,050
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #627989
Posted Wednesday, December 31, 2008 6:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:43 PM
Points: 2,109, Visits: 5,419
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/
Post #628014
Posted Wednesday, December 31, 2008 6:57 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 10:09 AM
Points: 15,661, Visits: 28,050
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #628030
Posted Thursday, January 1, 2009 7:11 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 7, 2014 7:19 AM
Points: 250, Visits: 537
Check out table functions as an option, depending of course on what you are trying to do.
Post #628428
Posted Friday, January 2, 2009 6:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 6, 2009 3:17 AM
Points: 18, Visits: 18
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.
Post #628771
Posted Friday, January 2, 2009 12:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:51 PM
Points: 7,139, Visits: 15,188
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?
Post #629038
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse