Possible to declare a variable inside a select statement?

[Draven74]

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:

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,

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:

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

 

[zhavic]

Just do it this way:

SELECT A.test1, A.test2,
    Case When B.Test3 <> 0 Then B.Test3
        Else C.Test4
        End As MyResult
FROM A
	CROSS JOIN B
	CROSS JOIN C

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[Draven74]

Thank you for the response.

Sorry, I guess I wasn't completely clear. When I wrote the nested select statement 'Select B.Test3 From B', I should've actually written 'Select TOP 1 B.Test3 From B Where ...'. So unfortunately I can't use the method you provided.

That's why I'm trying to use the @tmp variable, to avoid having to run the 'Select TOP 1 ...' again.

Any ideas would be greatly appreciated.
Thanks,

Draven

 

[zhavic]

OK,

At first, using subqueries in select list will always be slow.

You should use this:

SELECT A.test1, A.test2,
    Case When B.Test3 <> 0 Then B.Test3
        Else C.Test4
        End As MyResult
FROM A
	CROSS JOIN ( SELECT MAX( Test3 ) AS Test3 FROM B ) AS B
    CROSS JOIN C

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[zhavic]

Also to your original question.

This way it will work, but it always be slower than one right written select stament

DECLARE @myStr varchar(1000)
SELECT @myStr = 'DECLARE @tmp INT  SELECT @tmp = (SELECT B.test3 FROM B) SELECT A.test1, A.test2, CASE WHEN @tmp <> 0 THEN @tmp ELSE (SELECT C.test4 FROM C) END as myResult
FROM A'
EXEC (@myStr)

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.