CASE syntax

Question

Post reply

CASE syntax

Thomas Vanting

Valued Member

Points: 68
More actions
August 6, 2004 at 12:58 am

#86215

Hallo,
I have in a user defined function, this code:
CASE WHEN (LEN (CAST(@iRunNr AS NVARCHAR))) = 1 THEN SET @nvcRunNr = '00000' + CAST(@iRunNr AS NVARCHAR)
WHEN (LEN (CAST(@iRunNr AS NVARCHAR))) = 2 THEN SET @nvcRunNr = '0000' + CAST(@iRunNr AS NVARCHAR)
WHEN (LEN (CAST(@iRunNr AS NVARCHAR))) = 3 THEN SET @nvcRunNr = '000' + CAST(@iRunNr AS NVARCHAR)
WHEN (LEN (CAST(@iRunNr AS NVARCHAR))) = 4 THEN SET @nvcRunNr = '00' + CAST(@iRunNr AS NVARCHAR)
WHEN (LEN (CAST(@iRunNr AS NVARCHAR))) = 5 THEN SET @nvcRunNr = '0' + CAST(@iRunNr AS NVARCHAR)
WHEN (LEN (CAST(@iRunNr AS NVARCHAR))) = 0 THEN SET @nvcRunNr = CAST(@iRunNr AS NVARCHAR)
The ide is that if the variable @iRunNr is to small. Is should always be 6 characters, eg. 000001 or 001234, the case syntax should fill in zero's in the missing digits. The variable @iRunNr is an integer there for the CAST keyword.
When I run the code, error's come:
Server: Msg 156, Level 15, State 1, Line 49
Incorrect syntax near the keyword 'CASE'.
Server: Msg 156, Level 15, State 1, Line 50
Incorrect syntax near the keyword 'WHEN'.
Server: Msg 156, Level 15, State 1, Line 51
Incorrect syntax near the keyword 'WHEN'.
Server: Msg 156, Level 15, State 1, Line 52
Incorrect syntax near the keyword 'WHEN'.
Server: Msg 156, Level 15, State 1, Line 53
Incorrect syntax near the keyword 'WHEN'.
Server: Msg 156, Level 15, State 1, Line 54
Incorrect syntax near the keyword 'WHEN'.
Can someone tell me why, or if there is an other way to do this?
Thanks
Thomas Vanting

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Thomas Vanting Valued Member Points: 68 More actions · Answer 1

Hallo again,

I have found a solution, by using IF insted of CASE, this works.

Thomas Vanting

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Case can only be used within a select statement

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

David.Poole SSC Guru Points: 76001 More actions · Answer 3

CASE WHEN LEN(CAST((@iRunNr AS VARCHAR(10)))<6

THEN REPLICATE('0',6-LEN(CAST((@iRunNr AS VARCHAR(10))))+cast((@iRunNr as varchar(10))

ELSE cast((@iRunNr as varchar(10))

end

LinkedIn Profile