CODEWITHSUNDEEP
sql-server
b.g
b.g

Reputation: 411

Msg 102, Level 15, State 1 Incorrect syntax near '>'

I have a SQL Server Procedure like this:

//Beginning of procedure 
   SELECT 
          CASE IDNumber
                 WHEN (DATALENGTH(IDNumber)>7) 
                  THEN SUBSTRING(IDNumber,0,6)
                 WHEN (DATALENGTH(IDNumber) < 7)
                  THEN CONCAT((REPLICATE(0,7-LEN(IDNumber)),IDNumber)
          END AS NID

//Rest of the procedure here

the code on execution throws the error Incorrect syntax near '>'. on line WHEN (DATALENGTH(IDNumber)>7) .

IDNumber is a nvarchar. I tried using LEN(IDNumber) but in vain. I don't know what the error is!

Upvotes: 2

Views: 1600

Answers (2)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

Rewrite your query like that:

SELECT CASE 
        WHEN DATALENGTH(IDNumber) > 7 THEN SUBSTRING(IDNumber, 0, 6)
        WHEN DATALENGTH(IDNumber) < 7 THEN CONCAT(REPLICATE(0, LEN(IDNumber)), IDNumber)
    END AS NID;

When you write CASE Column, you have to compare it to direct values, not to an expression.

Upvotes: 3

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

If you have case variable then you need to provide values to When part, not the condition

Upvotes: 0

Related Questions