March 12, 2012 at 4:42 am
Hi All,
Can any one tell me whats wrong with the following code?
Declare @tbl_temp Table(ApplicationId int,ACPId int)
Declare @sqlquery varchar(max)
insert into @tbl_temp values(444,12215)
insert into @tbl_temp values(444,13801)
set @sqlquery='select * from Invoice I inner join @tbl_Temp T on I.ApplicaitonId=T.ApplicationID'
exec(@SqlQuery)
Its giving an error saying 'Must declare the table variable "@tbl_Temp". How to join the temporary table? Please advice..
March 12, 2012 at 4:55 am
The table variable has no visibility within the dynamic SQL, it's only visible in the scope that it's declared in.
That said, there is absolutely no need for dynamic SQL in this case.
Declare @tbl_temp Table(ApplicationId int,ACPId int)
insert into @tbl_temp values(444,12215)
insert into @tbl_temp values(444,13801)
select * from Invoice I inner join @tbl_Temp T on I.ApplicaitonId=T.ApplicationID
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2012 at 5:02 am
I have posted the Sample Code there.In the Original requirement the SQL statement is dynamically generated. It works if i do this way but it will be a recoding again for me as I have to use the same temporary table many times in the entire code.
Declare @sqlquery varchar(max)
set @sqlquery='Declare @tbl_temp Table(ApplicationId int,ACPId int)
insert into @tbl_temp values(444,12215)
insert into @tbl_temp values(444,13801)
select * from @tbl_temp'
exec(@SqlQuery)
Please let me know if there are any other alternatives.
March 12, 2012 at 5:12 am
instead of executing join query using variable..directly place the join statement after insert statements..this will work
March 12, 2012 at 5:25 am
If you are dynamically generating the sql why the need for a temporary table? or the exec statement?
The only way you can make a temporary table globally visible (not table varible that you declared) is to use create table ##tablename(column int)
This is potentially dangerous as multiple processes will have visibility of this table and may not work for you as it'll be consistant throughout you applications.
I think we need more information on why you've gone down this route in design as at the moment it could be that it will not work. At least not very efficiently.
March 12, 2012 at 5:29 am
Use a temp table instead of a table variable, that way it will be visible within the scope of the dynamic SQL (which I still see no reason for)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2012 at 5:29 am
Each dynamic SQL Statement that gets generated has to be joined with the Temporary table.
March 12, 2012 at 5:30 am
MysteryJimbo (3/12/2012)
The only way you can make a temporary table globally visible (not table varible that you declared) is to use create table ##tablename(column int)
Errr, no need for global temp tables here. Normal temp tables (CREATE TABLE #temp) are visible in the scope they are created in and any lower scopes, so create a temp table in the outer scope and it will be visible within the dynamic SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2012 at 5:31 am
If you absolutely need the dynamic SQL, this will work.
CREATE TABLE #tbl_temp (ApplicationId int,ACPId int)
Declare @sqlquery varchar(max)
insert into #tbl_temp values(444,12215)
insert into #tbl_temp values(444,13801)
set @sqlquery='select * from Invoice I inner join #tbl_Temp T on I.ApplicaitonId=T.ApplicationID'
exec(@SqlQuery)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2012 at 5:58 am
Thank you Gila..It worked for me.but just want to know Is there any disadvantage in using local temporary table?
March 12, 2012 at 6:07 am
You can try this..but I'm afraid this can be standard soln...go for ##table
Declare @sqlquery varchar(max)
Set @sqlquery='declare @tbl_temp table(applicationid int, avoid int)'
Set @sqlquery=@sqlquery+'insert into @tbl_temp values(444,12215) '
Set @sqlquey=@sqlquery+'insert into @tbl_temp values(444,1380)'
Set @sqlquery=@sqlquery+'select a.applicationid,a.acpid @tbl_temp a join invoice b on a.applicationid=b.applicationid'
Exec(@sqlquery)
March 12, 2012 at 6:55 am
chandrika.r 91171 (3/12/2012)
Thank you Gila..It worked for me.but just want to know Is there any disadvantage in using local temporary table?
No, it's almost the same as a table variable, may even perform better.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2012 at 6:55 am
gugan_ta (3/12/2012)
You can try this..but I'm afraid this can be standard soln...go for ##table
There is no need for a global temp table here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2012 at 10:57 pm
GilaMonster (3/12/2012)
gugan_ta (3/12/2012)
You can try this..but I'm afraid this can be standard soln...go for ##tableThere is no need for a global temp table here.
To add to what Gail said, if done incorrectly, the use of a global temp table can make concurrent runs impossible especially if the two runs are different and have different column requirements.
--Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply