Dynamic query in SQL Server 2005

Question

I am writing an dynamic Query in sqlserver 2005.

DECLARE @SQL3 VarChar(2000)                             
SET @SQL3 =' INSERT into @TableClub4 SELECT  ID from Clubcard   '  
print (@SQL3);
Exec (@SQL3);

Whenever it excutes it gives me an error

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@TableClub4".

But I have defined the @TableClub4 table parameter.

Let me know the syntax, where I am going wrong?

Arion · Accepted Answer · 2012-03-06 13:35:56Z

2

You cannot use a declared table inside a dynamic sql block. You can do it like this:

CREATE TABLE #TableClub4
(
    ID INT
)
DECLARE @SQL3 VarChar(2000)                             
SET @SQL3 =' INSERT into #TableClub4 SELECT ID from Clubcard'
print  (@SQL3);
Exec (@SQL3);
DROP TABLE #TableClub4

answered Mar 6, 2012 at 13:35

Arion

31.3k10 gold badges75 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Filip De Vos · Accepted Answer · 2012-03-06 13:48:35Z

1

Your table variable is not known inside the dynamic sql, but you can rewrite it to this:

DECLARE @SQL3 VarChar(2000)                             
SET @SQL3 ='SELECT  ID from Clubcard'  
print (@SQL3);

INSERT into @TableClub4
Exec (@SQL3);

answered Mar 6, 2012 at 13:48

Filip De Vos

12k1 gold badge50 silver badges62 bronze badges

Collectives™ on Stack Overflow

Dynamic query in SQL Server 2005

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related