How to hold CASE value for use in query

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)

How to hold CASE value for use in query

Author

Topic

kyle.spitzer
Starting Member

3 Posts

Posted - 2007-02-18 : 21:57:41

I have a select statement and I would like to know which when
clause(1st set of whens) was fired and the value of the then for
further use in my query. I am wondering if this is possibly, and how
it would be done. If i try and set a @variable at the beginning of
the case statament i get an error, here is my query

SELECT PIE.productID, PIE.quantity, CEILING((PIE.width/12.0)/0.5)*.5 as width, CEILING((PIE.length/12.0)/0.5)*.5 as length,
***throws an error****
@sqft = CASE
WHEN CEILING((PIE.width/12.0)/0.5)*.5 > (2.0 * (CEILING((PIE.length/12.0)/0.5)*.5))
THEN CEILING((((CEILING((PIE.width/12.0)/0.5)*.5) * 1.5) * CEILING((PIE.length/12.0)/0.5)*.5)/1.0)*1.0
WHEN CEILING((PIE.length/12.0)/0.5)*.5 > (2.0 * (CEILING((PIE.width/12.0)/0.5)*.5))
THEN CEILING((((CEILING((PIE.length/12.0)/0.5)*.5) * 1.5) * CEILING((PIE.width/12.0)/0.5)*.5)/1.0)*1.0
ELSE CEILING((CEILING((PIE.length/12.0)/0.5)*.5) * (CEILING((PIE.width/12.0)/0.5)*.5)/1.0)*1.0
END AS sqft

FROM ProductsInEstimate PIE
WHERE estID = @estid

I need the value @sqft for further use in my query but the method i have errors, is there a better way to do this or am i missing something??? Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-18 : 22:08:36

split into 2 query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-19 : 03:01:45

You can also make this a function, and return a table variable and do a CROSS APPLY.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources