|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Scope of temp tableimbeded in a dynamic query is not available to the code that executed the dynamic query. Why, and what can I do to make it work? I've tried using permanent tables, but then I run into permissions issues when others execute the stored procedures that create or drop the permanent tables. --this works Select * into #temp from data_table Select * from #temp --this works declare @ExecCmd char(256) set @ExecCmd = 'Select * from data_table' exec (@ExecCmd) --this does not work declare @ExecCmd char(256) set @ExecCmd = 'Select * into #temp from data_table' exec (@ExecCmd) select * from #temp EXEC() creates its own scope. Anything you create inside of EXEC() will not
be available once it is finished. This includes variables, temp tables, table variables, cursors, and use statements, to name a few. A couple of workarounds: (a) bypass the use of the temp table altogether. They are rarely required and you can probably generate the same result without it. (b) enclose everything inside of the EXEC() block. EXEC('Select * into #temp from data_table; select * from #temp') Show quote "Lauren" <Lau***@discussions.microsoft.com> wrote in message news:E70E0E72-5DCA-4ACE-BC47-BF32F0A3904D@microsoft.com... > The best to explain it is to give a code sample. Basically a #temp table > imbeded in a dynamic query is not available to the code that executed the > dynamic query. Why, and what can I do to make it work? I've tried using > permanent tables, but then I run into permissions issues when others > execute > the stored procedures that create or drop the permanent tables. > > --this works > Select * into #temp from data_table > Select * from #temp > > --this works > declare @ExecCmd char(256) > set @ExecCmd = 'Select * from data_table' > exec (@ExecCmd) > > --this does not work > declare @ExecCmd char(256) > set @ExecCmd = 'Select * into #temp from data_table' > exec (@ExecCmd) > select * from #temp > > exec switches the scope, so the temp table is contained within the confines
of the exec. It will work with a global temp table, however: --this will work declare @ExecCmd char(256) set @ExecCmd = 'Select * into ##temp from data_table' exec (@ExecCmd) select * from ##temp -- Show quoteGregory A. Beamer MVP; MCP: +I, SE, SD, DBA *************************** Think Outside the Box! *************************** "Lauren" wrote: > The best to explain it is to give a code sample. Basically a #temp table > imbeded in a dynamic query is not available to the code that executed the > dynamic query. Why, and what can I do to make it work? I've tried using > permanent tables, but then I run into permissions issues when others execute > the stored procedures that create or drop the permanent tables. > > --this works > Select * into #temp from data_table > Select * from #temp > > --this works > declare @ExecCmd char(256) > set @ExecCmd = 'Select * from data_table' > exec (@ExecCmd) > > --this does not work > declare @ExecCmd char(256) > set @ExecCmd = 'Select * into #temp from data_table' > exec (@ExecCmd) > select * from #temp > > > set @ExecCmd = 'Select * into ##temp from data_table' Ooh, this is dangerous. If two people call this stored procedure at roughly > exec (@ExecCmd) > select * from ##temp the same time, one of them will get: Server: Msg 2714, Level 16, State 6, Line 1 There is already an object named '##temp' in the database. And if one of them drops it while the other is still working on it, the second guy will get: Server: Msg 208, Level 16, State 1, Line 1 Invalid object name '##temp'. Never mind that even if the stars align and somehow they both manage to insert data (e.g. using INSERT not SELECT INTO), they will both have their own *and* each other's data, which could lead to results ranging anywhere from humorous to puzzling to inconvenient to bankruptcy. I never use global temp tables and have not yet found a good enough excuse to want to. Everything you can accomplish with them can also be accomplished with permanent tables (you still need to worry about potential naming conflicts in either case). But a permanent table is still available beyond the scope of EXEC(), for one. The only thing you get as a bonus with ##table is that it gets dropped on its own... but good code should drop either type directly anyway, IMHO. Lauren,
Temporary tables are visible to the process that created them and any subprocesses, but not at higher levels. Dynamic SQL should be a last resort, and it should be used with extreme care, particularly if any user input is incorporated into the query string, because of the risk of SQL injection. If you are quite sure you are not at risk, you can try one of these solutions: 1. Use permanent tables with unique names, like declare @uniquetablename char(33) set @uniquetablename = 'T'+replace(newid(),'-','') Then concatentate this name into your query string and operate on the resulting table using @uniquetablename in other queries. 2. (better) If you know the structure of the result set of the dynamic query, create the temp table and then use insert into #temp exec(@querystring) For more on the risk of using dynamic SQL: http://www.sommarskog.se/dynamic_sql.html Steve Kass Drew University Lauren wrote: Show quote >The best to explain it is to give a code sample. Basically a #temp table >imbeded in a dynamic query is not available to the code that executed the >dynamic query. Why, and what can I do to make it work? I've tried using >permanent tables, but then I run into permissions issues when others execute >the stored procedures that create or drop the permanent tables. > >--this works >Select * into #temp from data_table >Select * from #temp > >--this works >declare @ExecCmd char(256) >set @ExecCmd = 'Select * from data_table' >exec (@ExecCmd) > >--this does not work >declare @ExecCmd char(256) >set @ExecCmd = 'Select * into #temp from data_table' >exec (@ExecCmd) >select * from #temp > > > > One reason I use dynamic queries is that (with my limited knowledge of SQL),
I have found that DECLARE @data_date datetime SET @data_date = '7/1/05' --or some dynamic way of assigning value DECLARE @ExecCmd char(256) SET @ExecCmd = 'SELECT * FROM data_table WHERE date_field = ''@data_date'' EXEC (@ExecCmd) runs 10x faster than DECLARE @data_date datetime SET @data_date = '7/1/05' --or some dynamic way of assigning value SELECT * FROM data_table WHERE date_field = @data_date This is even more true the more complex the WHERE clause gets. I have had stored procedures that used to run 30 minutes with a standard query only take 3 minutes when run as a dynamic query. I'm not sure why, but I'm speculating that for every row of data evaluated, SQL has to retranslate the variable in the WHERE clause each time, and executing the already translated char string eliminates this (my queries are running angins 2 million + rows of data). Show quote "Steve Kass" wrote: > Lauren, > > Temporary tables are visible to the process that created them and any > subprocesses, but not at higher levels. > > Dynamic SQL should be a last resort, and it should be used with > extreme care, particularly if any user input is incorporated into the > query string, because of the risk of SQL injection. If you are quite > sure you are not at risk, you can try one of these solutions: > > 1. Use permanent tables with unique names, like > declare @uniquetablename char(33) > set @uniquetablename = 'T'+replace(newid(),'-','') > Then concatentate this name into your query string and operate > on the resulting table using @uniquetablename in other queries. > > 2. (better) If you know the structure of the result set of the > dynamic query, create the temp table and then use > insert into #temp exec(@querystring) > > For more on the risk of using dynamic SQL: > http://www.sommarskog.se/dynamic_sql.html > > Steve Kass > Drew University > > Lauren wrote: > > >The best to explain it is to give a code sample. Basically a #temp table > >imbeded in a dynamic query is not available to the code that executed the > >dynamic query. Why, and what can I do to make it work? I've tried using > >permanent tables, but then I run into permissions issues when others execute > >the stored procedures that create or drop the permanent tables. > > > >--this works > >Select * into #temp from data_table > >Select * from #temp > > > >--this works > >declare @ExecCmd char(256) > >set @ExecCmd = 'Select * from data_table' > >exec (@ExecCmd) > > > >--this does not work > >declare @ExecCmd char(256) > >set @ExecCmd = 'Select * into #temp from data_table' > >exec (@ExecCmd) > >select * from #temp > > > > > > > > > Lauren,
Typically, what you are seeing happens because a query plan is cached and reused when the parameter value is different. There are various ways to improve the situation, but I will admit that sometimes dynamic SQL is not a bad idea, and if your date is in a variable before you convert it and concatenate it into your query, you are playing it safe with respect to SQL injection. (But see further down for another theory on your situation.) (Note that an explicit CONVERT expression to a language-independent format, such as .... date_field = ''' + convert(char(8),@data_date,112) + '''' would be better in order to avoid any localization issues.) Other solutions are to reassign stored procedure parameters to local variables and use the local variables in the query, to use WITH RECOMPILE more often, and to be sure the column statistics for the date column are up to date or perhaps use a higher sampling percentage. It's also quite possible that some changes in indexing or other design factors can improve the situation also, though none of these methods can guarantee solving the problem. You'll find some information about this if you search groups.google.com for parameter-sniffing. If you are seeing this consistently, not just when the parameter value changes, the reason may be different. If date_field is not typed as datetime, you may be introducing a type conversion of the column value that makes it more difficult (or even impossible) to take advantage of an existing index on the date column. You can usually see which is the cause by looking at the query execution plan. You can also try a non-dynamic solution that would avoid type conversion of the column and see if it solves the problem. Here are two possible choices: DECLARE @data_date smalldatetime SET @data_date = '20050107' -- to be safe, specify the date literal string in a -- language independent format, or with an explicit convert SELECT * FROM data_table WHERE date_field = @data_date or DECLARE @data_date datetime SET @data_date = '20050107' DECLARE @data_date_string CHAR(8) SET @data_date_string = convert(char(8),@data_date,112) SELECT * FROM data_table WHERE date_field = @data_date_string These will be able to use an index whether the column type is smalldatetime or datetime. They also assume (per your example), that you have date-only values, not date+time-of-day. SK Lauren wrote: Show quote >One reason I use dynamic queries is that (with my limited knowledge of SQL), >I have found that > >DECLARE @data_date datetime >SET @data_date = '7/1/05' --or some dynamic way of assigning value >DECLARE @ExecCmd char(256) >SET @ExecCmd = 'SELECT * FROM data_table WHERE date_field = ''@data_date'' >EXEC (@ExecCmd) > >runs 10x faster than > >DECLARE @data_date datetime >SET @data_date = '7/1/05' --or some dynamic way of assigning value >SELECT * FROM data_table WHERE date_field = @data_date > >This is even more true the more complex the WHERE clause gets. I have had >stored procedures that used to run 30 minutes with a standard query only take >3 minutes when run as a dynamic query. I'm not sure why, but I'm speculating >that for every row of data evaluated, SQL has to retranslate the variable in >the WHERE clause each time, and executing the already translated char string >eliminates this (my queries are running angins 2 million + rows of data). > > > >"Steve Kass" wrote: > > > >>Lauren, >> >>Temporary tables are visible to the process that created them and any >>subprocesses, but not at higher levels. >> >>Dynamic SQL should be a last resort, and it should be used with >>extreme care, particularly if any user input is incorporated into the >>query string, because of the risk of SQL injection. If you are quite >>sure you are not at risk, you can try one of these solutions: >> >>1. Use permanent tables with unique names, like >>declare @uniquetablename char(33) >>set @uniquetablename = 'T'+replace(newid(),'-','') >>Then concatentate this name into your query string and operate >>on the resulting table using @uniquetablename in other queries. >> >>2. (better) If you know the structure of the result set of the >>dynamic query, create the temp table and then use >>insert into #temp exec(@querystring) >> >>For more on the risk of using dynamic SQL: >>http://www.sommarskog.se/dynamic_sql.html >> >>Steve Kass >>Drew University >> >>Lauren wrote: >> >> >> >>>The best to explain it is to give a code sample. Basically a #temp table >>>imbeded in a dynamic query is not available to the code that executed the >>>dynamic query. Why, and what can I do to make it work? I've tried using >>>permanent tables, but then I run into permissions issues when others execute >>>the stored procedures that create or drop the permanent tables. >>> >>>--this works >>>Select * into #temp from data_table >>>Select * from #temp >>> >>>--this works >>>declare @ExecCmd char(256) >>>set @ExecCmd = 'Select * from data_table' >>>exec (@ExecCmd) >>> >>>--this does not work >>>declare @ExecCmd char(256) >>>set @ExecCmd = 'Select * into #temp from data_table' >>>exec (@ExecCmd) >>>select * from #temp >>> >>> >>> >>> >>> >>> Thank you all for some good suggestions and warnings. I'll poke around with
them and see what works best. Show quote "Lauren" wrote: > The best to explain it is to give a code sample. Basically a #temp table > imbeded in a dynamic query is not available to the code that executed the > dynamic query. Why, and what can I do to make it work? I've tried using > permanent tables, but then I run into permissions issues when others execute > the stored procedures that create or drop the permanent tables. > > --this works > Select * into #temp from data_table > Select * from #temp > > --this works > declare @ExecCmd char(256) > set @ExecCmd = 'Select * from data_table' > exec (@ExecCmd) > > --this does not work > declare @ExecCmd char(256) > set @ExecCmd = 'Select * into #temp from data_table' > exec (@ExecCmd) > select * from #temp > >  |
|||||||||||||||||||||||
Other interesting topics