Possible to declare a variable inside a select statement?

[Draven74]

Hello,

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:
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

I hope that makes sense.
Thanks in advance

 

[DotNetGnat]

i guess you need to declare the variable outside and before the sql statement

-DNG

 

[Draven74]

Thank you very much for the quick reply.

Unfortunately it still doesn't work. I might not have explained the situation completely. The stored procedure is actually building a dynamic SQL satement and then assigning that to a varchar variable.

So it's more like:
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 to the beginning,

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.

 

[gmmastros]

If I understand your logic correctly, you should probably try this...

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

Of course, you're just throwing sql all over the place, and it can can ugly, and error prone. For example, if 'Select B.Test3 From B' or 'Select C.Test4 From C) either return multiple records, you will get the following error.

[red]
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
[/red]

It would be better to analyze how the tables are linked and re-write the query.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Draven74]

Wow, another quick response...thanks

Yup, you understood correctly. Funny that's actually what I had to do to make the query run, but I wanted to 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.