Incorrect syntax near the keyword 'CASE'

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

General SQL Server Forums

New to SQL Server Programming

Incorrect syntax near the keyword 'CASE'

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2012-07-03 : 10:34:33

[code]

declare @weddate datetime
declare @decades int
declare @years int
declare @months int
declare @days int
declare @minutes int
declare @seconds int
declare @sql nvarchar(2000)

set @weddate='2004-08-29 19:30:00.00'

--Calculate Decades
set @decades = datediff(year, @weddate,getutcdate())/10
select @decades
CASE
WHEN @decades = 0
THEN SET @sql = ''
ELSE SET @sql = @decades + ' decades'
END
select @decades
select @sql

[/code]

My plan is to have my code display a count of time from a specific date. For example "2 decades, 4 years, 10 months, 13 days, 5 hours and 6 minutes" is the ultimate desired output. I thought it would be a fun exercise but the thing is giving me fits I can't figure out.

The code runs FINE to the 'select @decades' line, but has a problem with the CASE statement. I don't see the problem. I've been trying to read booksonline and forums. What am I missing?

Craig Greenwood

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-03 : 10:53:41

In SQL, CASE is not a statement, it's an expression, meaning it returns a value. A simple rewrite should fix it:

--Calculate Decades
set @decades = datediff(year, @weddate,getutcdate())/10
select @sql=CASE 
	WHEN @decades = 0 
	THEN '' 
	ELSE @decades + ' decades' 
END
select @decades
select @sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources