May 1, 2009 at 6:44 am
HI all
I have some doubt in the execution of a query the query is as follows;;;;;;;;
DECLARE @VARIABLE VARCHAR(30)
DECLARE @QUERY VARCHAR(5)
SELECT @VARIABLE='TEST'
SELECT @QUERY='SELECT'+'''@VARIABLE'''
EXEC (@QUERY)
cAN ANYBODY EXPLAIN ME HOW THE SELECT COMMAND IS WORKING HERE TO ASSIGNING THE VALUE TO THE DIFFERENT VARIABLES DECLARED.
May 1, 2009 at 7:54 am
anu1krishna (5/1/2009)
HI allI have some doubt in the execution of a query the query is as follows;;;;;;;;
DECLARE @VARIABLE VARCHAR(30)
DECLARE @QUERY VARCHAR(5)
SELECT @VARIABLE='TEST'
SELECT @QUERY='SELECT'+'''@VARIABLE'''
EXEC (@QUERY)
cAN ANYBODY EXPLAIN ME HOW THE SELECT COMMAND IS WORKING HERE TO ASSIGNING THE VALUE TO THE DIFFERENT VARIABLES DECLARED.
This query is creating a dynamic SQL statement, in this case ..
SELECT 'TEST'[/CODE]
And by using the EXEC (@Variable) it is able to execute that statement. When Exec follows brackets, it can also execute SQL string. Please look up EXEC in BOL (Link).
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
May 12, 2009 at 12:48 am
cAN ANYBODY EXPLAIN ME HOW THE SELECT COMMAND IS WORKING HERE TO ASSIGNING THE VALUE TO THE DIFFERENT VARIABLES DECLARED.
The first Select simply assigns @VARIABLE a value of TEST
The second Select has repeated single-quote marks, each pair of which are interpreted as a literal single quote. So, it tries to assign @query a value of SELECT concatenated with '@VARIABLE', or SELECT'@VARIABLE'
Actually, this code looks like a "find the errors" exercise. Variable @query is defined as only five characters long, so it won't hold the final concatenation. Even with that changed to a larger number, the quote marks end up making the value of @variable moot -- the query would simply return the string "@VARIABLE".
Original code:DECLARE @VARIABLE VARCHAR(30)
DECLARE @QUERY VARCHAR(5)
SELECT @VARIABLE='TEST'
SELECT @QUERY='SELECT'+'''@VARIABLE'''
EXEC (@QUERY)
...gives result:SELEC
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'SELEC'.
Modify code to expand size of @query:
DECLARE @VARIABLE VARCHAR(30)
DECLARE @QUERY VARCHAR(30)
SELECT @VARIABLE='TEST'
SELECT @QUERY='SELECT'+'''@VARIABLE'''
EXEC (@QUERY)
...gives result:SELECT'@VARIABLE'
---------
@VARIABLE
(1 row(s) affected) Modify code by fixing the the quotes:
DECLARE @VARIABLE VARCHAR(30)
DECLARE @QUERY VARCHAR(30)
SELECT @VARIABLE='TEST'
SELECT @QUERY='SELECT'+ '''' + @VARIABLE + ''''
print @query
EXEC (@QUERY)
...gives:SELECT'TEST'
----
TEST
(1 row(s) affected)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply