Another CASE Issue - 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 2005 Forums

Transact-SQL (2005)

Another CASE Issue

Author

Topic

JDrown
Starting Member

10 Posts

Posted - 2008-03-04 : 06:30:17

Here is the very simple code to a function I am trying to write. In VBA it is downright simple. Where am I going wrong in T-SQL? Please help me identify my errors.

CREATE FUNCTION GetDescriptionMonth(@tiMonth TINYINT) RETURNS VARCHAR
AS
BEGIN
DECLARE @Month VARCHAR(9)

CASE @tiMonth
WHEN 1 THEN
@Month = 'January'

WHEN 2 THEN
@Month = 'February'

WHEN 3 THEN
@Month = 'March'

WHEN 4 THEN
@Month = 'April'

WHEN 5 THEN
@Month = 'May'

WHEN 6 THEN
@Month = 'June'

WHEN 7 THEN
@Month = 'July'

WHEN 8 THEN
@Month = 'August'

WHEN 9 THEN
@Month = 'September'

WHEN 10 THEN
@Month = 'October'

WHEN 11 THEN
@Month = 'November'

WHEN 12 THEN
@Month = 'December'

ELSE
@Month = 'UNKNOWN'
END

RETURN @Month
END

HERE ARE THE ERROR(S) RETURNED BY SQL SERVER, BUT I CAN'T MAKE MUCH USE OF THEM.

Msg 156, Level 15, State 1, Procedure GetDescriptionMonth, Line 6
Incorrect syntax near the keyword 'CASE'.

Msg 137, Level 15, State 2, Procedure GetDescriptionMonth, Line 46
Must declare the scalar variable "@Month".

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-04 : 06:33:00

[code]Select @Month =
CASE @tiMonth
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
...
End[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-04 : 06:34:08

Try this:-
CREATE FUNCTION GetDescriptionMonth(@tiMonth TINYINT) RETURNS VARCHAR
AS
BEGIN
DECLARE @Month VARCHAR(9)

SET @Month= CASE @tiMonth
WHEN 1 THEN 'January'

WHEN 2 THEN 'February'

WHEN 3 THEN 'March'

WHEN 4 THEN 'April'

WHEN 5 THEN 'May'

WHEN 6 THEN 'June'

WHEN 7 THEN 'July'

WHEN 8 THEN 'August'

WHEN 9 THEN 'September'

WHEN 10 THEN 'October'

WHEN 11 THEN 'November'

WHEN 12 THEN 'December'

ELSE 'UNKNOWN'
END

RETURN @Month
END

also not sure what you're doing here. but it seems like you can get the same result using DATENAME function available in T-SQL

JDrown
Starting Member

10 Posts

Posted - 2008-03-04 : 06:37:07

Ok, harsh_athalye, thank you very much. You're worth your weight in gold.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 06:40:51

[code]DECLARE @Month TINYINT

SET @Month = 2

SELECT DATENAME(MONTH, DATEADD(MONTH, @Month, '18991201'))[/code]

E 12°55'05.25"
N 56°04'39.16"

JDrown
Starting Member

10 Posts

Posted - 2008-03-04 : 06:59:36

Peso,

I was unaware of the DATENAME function, but that is exactly what I need and want. Learn two new things today, THANKS!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-04 : 07:45:53

quote:
Originally posted by Peso

DECLARE @Month TINYINT

SET @Month = 2

SELECT DATENAME(MONTH, DATEADD(MONTH, @Month, '18991201'))

E 12°55'05.25"
N 56°04'39.16"

This is exactly what I usually suggest to my colleagues

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 08:09:31

I haven't seen you down the corridors here, Madhi

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-04 : 08:13:59

From month name to month number

declare @month char(3)
set @month='Mar'
select month(cast(@month+' 2000' as datetime))

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources