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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 EXEC(@sql) problem
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dcarva
Posting Yak Master

USA
140 Posts

Posted - 04/30/2006 :  21:37:14  Show Profile  Reply with Quote
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

USA
140 Posts

Posted - 04/30/2006 :  23:10:27  Show Profile  Reply with Quote
I had to put brackets around @Results:

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

Thanks
Go to Top of Page

dcarva
Posting Yak Master

USA
140 Posts

Posted - 05/01/2006 :  00:18:07  Show Profile  Reply with Quote
No, that wasn't it. I still got the same error. I had to append the 'DECLARE TABLE' statement into the query.

Thanks

Edited by - dcarva on 05/01/2006 00:29:16
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 05/01/2006 :  00:36:49  Show Profile  Reply with Quote
Why do you need to use Dynamic SQL ? How do you intend to use the tabl variabl @Result ?



KH


Go to Top of Page

sshelper
Posting Yak Master

216 Posts

Posted - 05/01/2006 :  07:51:47  Show Profile  Visit sshelper's Homepage  Reply with Quote
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
Go to Top of Page

Srinika
Flowing Fount of Yak Knowledge

Sri Lanka
1378 Posts

Posted - 05/01/2006 :  08:03:22  Show Profile  Reply with Quote
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
Go to Top of Page

dcarva
Posting Yak Master

USA
140 Posts

Posted - 05/01/2006 :  12:05:33  Show Profile  Reply with Quote
Thanks to all for the replies. You guys are right. I am no longer using dynamic sql to do this.

Thanks!
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000