EXEC(@sql) problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

EXEC(@sql) problem

Author

Topic

dcarva
Posting Yak Master

140 Posts

Posted - 2006-04-30 : 21:37:14

Hello,

I am building t-sql in a stored procedure dynamically. @Results is a table variable. The following code compiles in the stored proc, but .NET tells me that I have to declare the variable @results...although it is already declared as a table variable. Ex:

DECLARE @Results TABLE
(
TempId int IDENTITY PRIMARY KEY,
ProductId int NOT NULL
...etc....
)

SET @sql =
'INSERT INTO @Results ' +
'select ... '

EXEC ( @sql )

Is this not possible? Is there something else I need to do to this stored procedure?
Thanks

dcarva
Posting Yak Master

140 Posts

Posted - 2006-04-30 : 23:10:27

I had to put brackets around @Results:

SET @sql =
'INSERT INTO [@Results] ' +
'select ... '

Thanks

dcarva
Posting Yak Master

140 Posts

Posted - 2006-05-01 : 00:18:07

No, that wasn't it. I still got the same error. I had to append the 'DECLARE TABLE' statement into the query.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-01 : 00:36:49

Why do you need to use Dynamic SQL ? How do you intend to use the tabl variabl @Result ?

sshelper
Posting Yak Master

216 Posts

Posted - 2006-05-01 : 07:51:47

Given the short description of what you're trying to do, you have to use a global temporary table and not a table variable because the table variable is only visible in the context of the stored procedure and not inside the query you are building.

SQL Server Helper
http://www.sql-server-helper.com

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-01 : 08:03:22

dcarva,

What u r trying to do is use dynamic SQL, which is not the recommended practice, unless its a must.

U may achieve the same as follows:

DECLARE @Results TABLE
(
a int ,
b int 
)

INSERT INTO @Results 
select 4,7



Select * from @Results

Srinika

dcarva
Posting Yak Master

140 Posts

Posted - 2006-05-01 : 12:05:33

Thanks to all for the replies. You guys are right. I am no longer using dynamic sql to do this.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources