Hello,
I've got a stored procedure that builds a dynamic SQL satement and then assigns it to a varchar variable.
I would like to know if if's possible to declare a variable from within a select statement. I keep getting the 'Incorrect syntax near the keyword Declare' error.
For example:
I tried moving the Declare (@tmp) to the beginning,
but it then gave off an incorrect syntax near the keyword SELECT error (referring to the SELECT @tmp). Same thing if I changed it to SET.
I know I could just do:
But basically what I'm trying to do is use the @tmp variable to avoid having to run the statement (Select B.Test3 From B) again if @tmp wasn't empty (in hopes of speeding up the query). The statements are only pulling back one record, so that shouldn't be a problem.
I hope that makes sense. Anyone got any ideas?
Thanks in advance
I've got a stored procedure that builds a dynamic SQL satement and then assigns it to a varchar variable.
I would like to know if if's possible to declare a variable from within a select statement. I keep getting the 'Incorrect syntax near the keyword Declare' error.
For example:
Code:
DECLARE @myStr varchar(1000)
SELECT @myStr = 'SELECT A.test1, A.test2, (DECLARE @tmp INT SELECT @tmp = (SELECT B.test3 FROM B) CASE WHEN @tmp <> 0 THEN @tmp ELSE (SELECT C.test4 FROM C) END) as myResult FROM A'
EXEC (@myStr)
I tried moving the Declare (@tmp) to the beginning,
Code:
DECLARE @myStr varchar(1000)
SELECT @myStr = 'DECLARE @tmp INT SELECT A.test1, A.test2, (SELECT @tmp = (SELECT B.test3 FROM B) CASE WHEN @tmp <> 0 THEN @tmp ELSE (SELECT C.test4 FROM C) END) as myResult
FROM A'
EXEC (@myStr)
but it then gave off an incorrect syntax near the keyword SELECT error (referring to the SELECT @tmp). Same thing if I changed it to SET.
I know I could just do:
Code:
SELECT A.test1, A.test2,
Case When (Select B.Test3 From B) <> 0 Then
(select B.Test3 From B)
Else
(Select C.Test4 From C)
End As MyResult
FROM A
But basically what I'm trying to do is use the @tmp variable to avoid having to run the statement (Select B.Test3 From B) again if @tmp wasn't empty (in hopes of speeding up the query). The statements are only pulling back one record, so that shouldn't be a problem.
I hope that makes sense. Anyone got any ideas?
Thanks in advance