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

sp_executesql output parameter as table variable Expand / Collapse
Author
Message
Posted Tuesday, January 18, 2011 10:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:28 AM
Points: 380, Visits: 899
Hi All
I was just thinking of using a table variable instead of temp table to see if it gives any performance gain(no of records is less and I dont need any indexing on temp table) in a dynamic sql.
This is what I was trying to achieve:
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @tabvarO table(id int)
DECLARE @Lastlname varchar(30)
SET @SQLString = N'DECLARE @tabvar table(id int)
insert into @tabvar SELECT account_card_id
FROM t_account_card WHERE last_name = @Lastlname'
SET @ParmDefinition = N'@Lastlname varchar(30),@tabvar table(id int) OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@Lastlname = 'smith',
@tabvar=@tabvarO OUTPUT
select * from @tabvarO

but I cant get the output in table variable and get error:
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@tabvarO".


I also understand that scope of variable for dynamic sql is for that statement only and that cannot be used outside the statement. But I'll appreciate any other inputs that might make it possible to get table variable out using dynamic sql.

thanks



-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
Post #1049526
Posted Wednesday, January 19, 2011 3:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 850, Visits: 5,583
To pass a table variable you have to create a type and define the table as the type.

What you are trying to do will not work as table variables can only be passed as READONLY.
Post #1049898
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse