set variable within a select from ()

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

set variable within a select from ()

Author

Topic

jbullough
Starting Member

4 Posts

Posted - 2012-04-25 : 05:14:21

I would have thought this should be a fairly simple thing to do, but in all my searching I can not find a solutions hence this post.
I am trying to set a variable within a select table and use a case statement to return a fixed result for a field. with my code below, it fails - i have trimmed this down to as basic as I can make it.

declare @myvar INT;
select thisid from (
select @myvar=(select top 1 id from somedb);
select @myvar as thisid;
) as temptable

I get an error stating 'Incorrect syntax near'=''
If i remove the outer select statement, then it will work.

This code is trimmed from a much larger code which has a lot of duplication in it which I am trying to remove the duplication by the use of setting a variable. At the moment the full code does a CASE WHEN (SELECT statement)>={some figure} THEN (SELECT statement) ELSE (-1) END, so as you can see, I am running the same SELECT statement twice, bulking out the code and slowing it down as running the SELECT statement twice - If I can return the first SELECT statement into a variable for comparison in the CASE statement, then I could simply return the variable instead of having to run the query again to return the same data.

If I can get the above working, I can rectify the rest of my code to trim it down significantly.

Any ideas greatfully appreciated.
Thanks in advance, Jason

Sachin.Nand

2937 Posts

Posted - 2012-04-25 : 05:23:36

This ?


declare @myvar INT; 
select @myvar=thisid from 
(
select top 1 id from somedb;

) as temptable

After Monday and Tuesday even the calendar says W T F ....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-25 : 05:49:00

select top 1 @myvar=id from somedb;

Also note that TOP is meaningless without ORDER BY clause

Madhivanan

Failing to plan is Planning to fail

jbullough
Starting Member

4 Posts

Posted - 2012-04-25 : 05:54:42

thanks for that, but I really need to have the set variable inside the first select statement. Take this example instead which may help to further my problem, again the code has been abbreviated somewhat, but this shows the logic of what I am after:
SELECT thisid FROM (
SELECT (CASE WHEN (SELECT TOP 1 ID FROM Employee)>=1
THEN (SELECT TOP 1 ID FROM Employee)
ELSE (1)
END
) AS thisid
) AS temptable

As you can see, I am running the same SELECT statement on the inside, twice - it would be more sensible to make the following:
DECLARE @myvar INT;
SELECT thisid FROM (
SELECT (CASE WHEN (@myvar=(SELECT TOP 1 ID FROM Employee))>=1
THEN (@myvar)
ELSE (1)
END
) AS thisid
) AS temptable

OR

DECLARE @myvar INT;
SELECT thisid FROM (
SELECT (SET{ or select} @myvar=(SELECT TOP 1 ID FROM Employee);
CASE WHEN @myvar>=1
THEN (@myvar)
ELSE (1)
END
) AS thisid
) AS temptable

Regards
Jason

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-25 : 06:11:19

[code]SELECT CASE
WHEN ID >= 1 THEN ID
ELSE 1
END
FROM (
SELECT TOP(1) ID
FROM dbo.Employee
ORDER BY { Some column(s) }
) AS d [/code]

N 56°04'39.26"
E 12°55'05.63"

jbullough
Starting Member

4 Posts

Posted - 2012-04-25 : 09:43:44

hmm, I like your thinking SwePeso - I will see if I can re-write some how to accomodate that logic.
I will let you know if I have success or not!
Thanks
Jason

jbullough
Starting Member

4 Posts

Posted - 2012-04-25 : 10:34:46

Just to let you know, that SwePeso's suggestion was the fix.
Thanks SwePeso - just a little lateral thinking is all it took eh?!

Shame I couldn't see it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources