August 6, 2004 at 12:58 am
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
August 6, 2004 at 1:28 am
Hallo again,
I have found a solution, by using IF insted of CASE, this works.
Thomas Vanting
August 6, 2004 at 3:43 am
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
August 6, 2004 at 3:44 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply