CODEWITHSUNDEEP
sqlsql-server
user3708795
user3708795

Reputation: 23

What is wrong with the syntax on this case statement?

I am trying to insert values into a temp table via a cursor and if they have a period on the end, I would like to remove the period. Here is the code I am having syntax issues with. 

WHILE @@FETCH_STATUS = 0
BEGIN
    CASE SELECT CHARINDEX('.',REVERSE(@Category))  
        WHEN 1
        THEN INSERT INTO #Category VALUES (SUBSTRING(@Category,1,LEN(@Category)-1)))
END;

What am I doing incorrectly here? I'm open to more efficient answers but would also like to know how to solve it this way.

Upvotes: 0

Views: 44

Answers (2)

D Stanley
D Stanley

Reputation: 152521

CASE is used to compute an expression, not control program flow. Use IF instead:

WHILE @@FETCH_STATUS = 0
BEGIN
    IF CHARINDEX('.',REVERSE(@Category)) = 1
        INSERT INTO #Category VALUES (SUBSTRING(@Category,1,LEN(@Category)-1)))
END;

Although if @Category is computed from an expression off of your cursor then you may see significant improvement by using an INSERT INTO instead of looping and executing individual INSERT statements.

Upvotes: 3

Heinzi
Heinzi

Reputation: 172220

CASE yields an expression, not a statement. You are looking for the IF statement.

As for a better way to do this: Scrap the cursor and use an INSERT INTO ... SELECT ... WHERE CHARINDEX('.', REVERSE(category)) = 1 statement.

Upvotes: 3

Related Questions