using CASE to set vars - SQL Server Forums

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 2012 Forums

Transact-SQL (2012)

using CASE to set vars

Author

Topic

lcsgeek
Starting Member

38 Posts

Posted - 2013-01-28 : 11:25:21

doesn't look like the following is allowed:


SELECT CASE
	WHEN DATEPART(wk,@ffTestStartDate) BETWEEN 27 AND 47 THEN --middle of year to end of first tri
		SET @dwSeasonKey = 4 --Fall
	WHEN DATEPART(wk,@ffTestStartDate) BETWEEN 11 AND 26 THEN --end of second tri to middle of year
		SET @dwSeasonKey = 2 --Spring
	ELSE
		SET @dwSeasonKey = 1 --Winter
END

using IF is rediculously ugly. Is there something I'm missing with CASE? Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-28 : 11:29:12

The syntax is as follows:

SELECT @dwSeasonKey = 
CASE
	WHEN DATEPART(wk,@ffTestStartDate) BETWEEN 27 AND 47 THEN --middle of year to end of first tri
		4 --Fall
	WHEN DATEPART(wk,@ffTestStartDate) BETWEEN 11 AND 26 THEN --end of second tri to middle of year
		2 --Spring
	ELSE
		1 --Winter
END

lcsgeek
Starting Member

38 Posts

Posted - 2013-01-28 : 12:37:42

Thanks James, much appreciated.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-28 : 13:09:36

You are very welcome - glad to be of help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-28 : 13:17:30

just FYI
Another SQL 2012 way to do this is by using IIF


SELECT @dwSeasonKey = IIF(DATEPART(wk,@ffTestStartDate) BETWEEN 27 AND 47,4,IIF(DATEPART(wk,@ffTestStartDate) BETWEEN 11 AND 26,2,1))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources