June 12, 2009 at 3:39 am
drop table document
drop table folder_document
create table document (id int,name varchar(30))
create table folder_document (id int,name varchar(30),document int)
declare @sql nvarchar(max)
declare @docTable table(id int);
insert into @docTable values(1)
insert into @docTable values(2)
insert into @docTable values(3)
insert into @docTable values(4)
select * from @docTable
set @sql='
select rd.id,rd.name,fd.name
from document rd left outer join folder_document fd on rd.id=fd.document
where rd.id in (select id from @docTable)'
exec sp_executesql @sql
Msg 1087, Level 15, State 2, Line 4
Must declare the table variable "@docTable".
June 12, 2009 at 4:40 am
The SQL being run by sp_executesql is executed in a different process to the rest of your script, so @docTable isn't available to sp_executesql.
You will need to include the creation of @docTable in the dynamic sql i.e.
set @sql='
declare @docTable table(id int);
insert into @docTable values(1)
insert into @docTable values(2)
insert into @docTable values(3)
insert into @docTable values(4)
select * from @docTable
select rd.id,rd.name,fd.name
from document rd left outer join folder_document fd on rd.id=fd.document
where rd.id in (select id from @docTable)'
June 12, 2009 at 6:20 am
Can you tell me, why you are going for dynamic query as per your script...
June 12, 2009 at 6:32 am
Variables that are declared outside of the dynamic SQL are not known in the dynamic SQL. You’ll have to change your code to work with temporary table instead of table variable or to include the creation of the table variable and the insert statement to the table in the dynamic SQL. Notice that using dynamic SQL has its own problems (especially regarding security and permissions) and you’ll might want to find another way to do what you want to do.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 12, 2009 at 7:45 am
I have generated the sample code but actually in last select statement select fields names come from another dynamic table which one is based on physical table...............
dont worry i have finished with Temporary variables.........and my work done
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply