Case Syntax

[SQLNovice]

Hi fellow-developers,

I am literally hitting a wall trying to get the syntax right for this case statement.

Could someone please point out where I am going wrong?

Thanks in advance :)

DECLARE @cStr AS varchar(50)
DECLARE @nNum INT

SET @nNum = 0
SET @cStr = 'XX'
SET @nNum = (year(getdate()) - 2007)
print ' num P = ' + cast(@nNum as char)

SELECT CASE
WHEN (@nNum = 0) THEN SET @cStr = @cStr + 'CY'
WHEN (@nNum <> 0) THEN RAISERROR ('xxx', 16, 1)
WHEN (@nNum >= 10) THEN RAISERROR ('yyy.', 16, 1)
WHEN (@nNum < 10) THEN SET @cStr = @cStr + Replicate ('P', @nNum) + 'Y'
END

[Motoma]

I think what you really want is this:

DECLARE @cStr AS varchar(50)
DECLARE @nNum INT
 
SET @nNum = 0
SET @cStr = 'XX'
SET @nNum = (year(getdate()) - 2007)
print ' num P = ' + cast(@nNum as char)
 
 
IF (@nNum <> 0) RAISERROR ('xxx', 16, 1)
IF (@nNum >= 10) RAISERROR ('yyy.', 16, 1)
 
SELECT CASE
WHEN (@nNum = 0) THEN @cStr + 'CY'
WHEN (@nNum < 10) THEN @cStr + Replicate ('P', @nNum) + 'Y'
END
 

You see, you had a SELECT statement with a CASE statement inside. At runtime, this will be replaced with the actual case that it falls under. Your code would have generated the equivalent of

SELECT SET @cStr = @cStr + 'CY'
 

Which is not a valid SELECT statement.

[SQLNovice]

Thank you very much for your response, The procedure works perfectly :) Thanks also for providing an explanation on why an error was being returned.

Regards,
-AM

I think what you really want is this:

DECLARE @cStr AS varchar(50)
DECLARE @nNum INT
 
SET @nNum = 0
SET @cStr = 'XX'
SET @nNum = (year(getdate()) - 2007)
print ' num P = ' + cast(@nNum as char)
 
 
IF (@nNum <> 0) RAISERROR ('xxx', 16, 1)
IF (@nNum >= 10) RAISERROR ('yyy.', 16, 1)
 
SELECT CASE
WHEN (@nNum = 0) THEN @cStr + 'CY'
WHEN (@nNum < 10) THEN @cStr + Replicate ('P', @nNum) + 'Y'
END
 

You see, you had a SELECT statement with a CASE statement inside. At runtime, this will be replaced with the actual case that it falls under. Your code would have generated the equivalent of

SELECT SET @cStr = @cStr + 'CY'
 

Which is not a valid SELECT statement.

[Motoma]

You are welcome. I am glad everything worked out for you.

Thank you very much for your response, The procedure works perfectly :) Thanks also for providing an explanation on why an error was being returned.

Regards,
-AM