November 23, 2010 at 1:55 pm
Hi,
I have a need to Script a where clause from client side and send it to the server to execute in dynamic sql.
The issue is that the table it needs to execute on has to be either a #table or a @table (prefer the latter).
BUT I get errors with @tables and #tables in basic dynamic sql see here
DECLARE @test-2 TABLE
(
Symbol VARCHAR(10),
Price FLOAT
)
INSERT INTO @test-2(Symbol,Price) VALUES('AA',5.26)
INSERT INTO @test-2(Symbol,Price) VALUES('BB',1.06)
INSERT INTO @test-2(Symbol,Price) VALUES('CC',2.38)
INSERT INTO @test-2(Symbol,Price) VALUES('EE',7.20)
--THIS WORKS
--SELECT * FROM @test-2
DECLARE @SQL VARCHAR(1000)
DECLARE @Where VARCHAR(500)
SET @Where= 'Price >3.0'
SET @SQL = 'SELECT Symbol,Price FROM' +@test + ' '+@Where
EXEC(@SQL)
Error: Must declare the scalar variable "@test"
How can I get a tempoary table into dynamic sql, for a scripted @Where clause from the client.:w00t:
Thanks:-)
November 23, 2010 at 2:07 pm
In this particular case, the answer is straightforward:
Before
EXEC(@SQL)
Put PRINT @SQL
and see what it returns 😉
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
November 23, 2010 at 2:24 pm
The scope of a table variable is the current batch only. It is NOT in scope to any nested procedures, including dynamic sql. So, if you want to use it in dynamic sql, it must be declared, populated, and manipulated all from within that one piece of dynamic sql.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 23, 2010 at 2:30 pm
DECLARE @SQL VARCHAR(1000)DECLARE @Where VARCHAR(500)
SET @Where= 'Price >3.0'
SET @SQL = 'SELECT Symbol,Price FROM' +@test + ' '+@Where
In this, you are trying to concatenate a string and a table variable together. Can't be done.
What you would want to do is like this:
SET @SQL = 'SELECT Symbol, Price FROM @test-2 WHERE ' + @where;
Note that I included the "WHERE" keyword that you omitted from your code.
However, read my previous post about the scope of a table variable. Change to using a temporary table, and this will work.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 23, 2010 at 2:34 pm
When you run dynamic sql, it runs in it's own scope. I don't think you'll be able to use the table variable, but a temp table should work.
create table #test (
Symbol VARCHAR(10),
Price FLOAT
)
INSERT INTO #Test(Symbol,Price) VALUES('AA',5.26)
INSERT INTO #Test(Symbol,Price) VALUES('BB',1.06)
INSERT INTO #Test(Symbol,Price) VALUES('CC',2.38)
INSERT INTO #Test(Symbol,Price) VALUES('EE',7.20)
--THIS WORKS
--SELECT * FROM @test-2
DECLARE @SQL VARCHAR(1000)
DECLARE @Where VARCHAR(500)
SET @Where= 'where Price >3.0'
SET @SQL = 'SELECT Symbol,Price FROM #test ' + @Where
Print @SQL
EXEC(@SQL)
drop table #test
November 23, 2010 at 2:37 pm
Digs (11/23/2010)
Hi,I have a need to Script a where clause from client side and send it to the server to execute in dynamic sql.
The issue is that the table it needs to execute on has to be either a #table or a @table (prefer the latter).
...
What's the specific reason for you not to use global temp tables (##table) and why do you prefer the table variable?
The reason you can't use a local temp table in a dynamic sql statement (straight from BOL):
Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced.
The dynamic sql statement will run using a separate session. Therefore it cannot reference the local temp table.
Workaround: reference a permanent table or a global temp table. But in case you need to cover parallel processing (e.g. the sproc is called by two different processes), it's getting a little more complex since you would need to uniquely identify the sproc referencing the correct data and to clean up afterwards.
November 23, 2010 at 3:16 pm
Thanks all for a response...:-)
I will try to be clear on my intended purpose...
I am pulling data up from a VARCHAR(MAX) field that is stored like this
data|data|data|etc
I turn this string into table using a fnDelimitedSplit table function found on this forum
ITEMID, ITEM
1, data
2, data
3, data
4, data
The above can be 500 rows max long
The data will be used by the client to meet a data scanner need.
I will go with the #Test table and the PRINT tsql
NOTE: I am assume the PRINT code can go into a STORE PROC
Thanks..:-):-D:-)
After testing all seams to work ok !
November 23, 2010 at 3:41 pm
The task seems to be straightforward.
What would you need the dynamic sql for?
Couldn't you just pass the VARCHAR(MAX) value, the @where variable and the call of fnDelimitedSplit to the dynamic string?
November 23, 2010 at 3:45 pm
LutzM (11/23/2010)
The reason you can't use a local temp table in a dynamic sql statement (straight from BOL):Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced.
The dynamic sql statement will run using a separate session. Therefore it cannot reference the local temp table.
Lutz, local temporary tables are visible to any code in the same connection from the point the table is created, including nested stored procedure and dynamic sql.
For instance:
CREATE TABLE #test (name sysname);
EXEC ('INSERT INTO #test SELECT name FROM sys.objects');
SELECT * FROM #test;
As long as it's created before the dynamic sql, it will work.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 23, 2010 at 5:27 pm
Couldn't you just pass the VARCHAR(MAX) value, the @where variable and the call of fnDelimitedSplit to the dynamic string?
The real code is more complex, thanks Lutz!:-)
November 23, 2010 at 10:17 pm
LutzM (11/23/2010)
What's the specific reason for you not to use global temp tables (##table)
Concurrency and logical separation of data between concurrent runs because all sprocs that use it see the same table.
A normal temp table CAN be declared in a sproc and then used by dynamic SQL within the sproc.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2010 at 12:57 pm
Jeff Moden (11/23/2010)
LutzM (11/23/2010)
What's the specific reason for you not to use global temp tables (##table)Concurrency and logical separation of data between concurrent runs because all sprocs that use it see the same table.
A normal temp table CAN be declared in a sproc and then used by dynamic SQL within the sproc.
Jeff and Wayne:
You've just seen an example of the consequences someone has to face after CWI (coding while intoxicated).
First I totally messed up the scope of temp table vs. table variables and after that it got even worse by mixing connections with batches.
:blush: THANK YOU FOR PUTTING IT STRAIGHT!! :blush:
Btw: Anybody has the proper DBCC TIMEWARP parameter handy? Just need to go less than 24hrs back...
You know what I just figured: A hole is really amazing: It's getting bigger if you take something out of it. *still digging*
November 24, 2010 at 2:38 pm
Heh... not to worry, Lutz. You've seen the mess I can make of things if I PBC (Post Before Coffee). 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2010 at 3:18 pm
Jeff Moden (11/24/2010)
Heh... not to worry, Lutz. You've seen the mess I can make of things if I PBC (Post Before Coffee). 😀
No, I haven't. I barely can remember a few threads where one of the other "supposed-to-be-referenced-as-a-guru" did find a minor thing to improve your code. But a mess???? Never. Ever.(AFAICT)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply