Using table-variables in dynamic sql

Question

Using table-variables in dynamic sql

Edmund Ward

SSChasing Mays

Points: 620
More actions
August 24, 2007 at 10:48 am

#197521

I'm trying to write a stored proc that allows me to get some data from a linked server. If I write the following code, the procedure creates successfully:
CREATE PROCEDURE [dbo].[sprocDealSelectDtlItem]
@dealid in
@dealerid smallint,
@stocknum int
AS
DECLARE
    @sql
nvarchar(4000),
    @paramlist
nvarchar(4000)
    -- Construct the main sql statement
    SELECT @sql = 'INSERT INTO @mytable SELECT * FROM OPENQUERY(MBZODBC, ''SELECT
        vs.StockbookNumber,
        vs.VehicleDescriptio001
      FROM
      {oj VS_15_UsedVehicleStock vs
      LEFT OUTER JOIN GB_00_UserDetails u ON (vs.SalesExecutive001 = u.UserID)}
      WHERE
      vs.StockbookNumber = @stocknum'')'

    DECLARE @mytable table (
      StockbookNumber
int PRIMARY KEY,
      VehicleDescriptio001
nvarchar(50))
    EXEC (@sql)
When I try to run this sproc with the following:
EXEC
sprocDealSelectDtlItem @dealId=1, @dealerId=30, @stocknum=500001
I get the following error:
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@mytable".
Anybody know how to pass a table variable to dynamic sql? I tried using a @paramlist with sp_executesql but I still got the same undeclared variable error, but while trying to create the stored procedure and referring this time to the parameter to sp_executesql. Is there another way to do this? I've wasted a day fiddling with the syntax to try to get this to work. Please somebody help me!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 1

Execute statements run under a different context, and table variables are not shared. You could try using a temporary table.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Andras Belokosztolszki SSC-Insane Points: 21971 More actions · Answer 2

Also see http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=149&messageid=393025

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Why does this need to be dynamic SQL? I don't see anything in your code that would require dynamic SQL to accomplish.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Edmund Ward SSChasing Mays Points: 620 More actions · Answer 4

John, thanks for your reply. I've simplified the query significantly to focus on the part that's causing the error. Both the linked server and the main table (the vs alias) are dynamically added to the query in my full version. I've printed out the SQL statement just before execution and using it statically (i.e. not dynamically) it works fine.

Edmund Ward SSChasing Mays Points: 620 More actions · Answer 5

Thanks, Andras. Using temporary table seems to work OK.