SQL Server Forums
Profile | Register | 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		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dcarva
Posting Yak Master

USA
136 Posts
Posted - 04/30/2006 :  21:37:14  Show Profile  Visit dcarva's Homepage  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
136 Posts
Posted - 04/30/2006 :  23:10:27  Show Profile  Visit dcarva's Homepage  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
136 Posts
Posted - 05/01/2006 :  00:18:07  Show Profile  Visit dcarva's Homepage  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
16745 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

213 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
136 Posts
Posted - 05/01/2006 :  12:05:33  Show Profile  Visit dcarva's Homepage  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  
 New 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.06 seconds. Powered By: Snitz Forums 2000