SQL 2005 Case Statement syntax

Posted on 2013-10-01
Medium Priority
Last Modified: 2013-10-01
I'm trying to run the following script but am getting an error
                      Msg 156, Level 15, State 1, Line 7
                       Incorrect syntax near the keyword 'like'.

The intent is if @AcctNumb1='Admin' then select all customers from the RM00101 table otherwise select either the customer number in @AcctNumb1 or @AcctNumb2

declare @AcctNumb1 varchar (31)
declare @AcctNumb2 varchar (31)
set @AcctNumb1='Admin'
set @AcctNumb2='12345'

select * from rm00101 a
      where (case when @AcctNumb1='ADMIN' then a.custnmbr like '%'
            else a.custnmbr = @AcctNumb1+'%'
                  or a.custnmbr = @AcctNumb2+'%' end)

Is "Like" not valid in this context?

Question by:jdr0606
LVL 34

Expert Comment

by:Brian Crowe
ID: 39538360
You should convert this to a boolean logic equivalent but I don't understand

a.custnmbr = @AcctNumb1+'%'
LVL 72

Accepted Solution

Qlemo earned 2000 total points
ID: 39538370
You can't code a condition into the THEN of a CASE. CASE needs to return an expression (and conditions aren't expressions). You can write it like this:
declare @AcctNumb1 varchar (31)
declare @AcctNumb2 varchar (31)
set @AcctNumb1='Admin'
set @AcctNumb2='12345'

select * from rm00101 a
      where @AcctNumb1='ADMIN' and a.custnmbr like '%' 
        or @AcctNumb1 != 'ADMIN' and (a.custnmbr = @AcctNumb1+'%'
                  or a.custnmbr = @AcctNumb2+'%')

Open in new window

Though I doubt the percent is correct for @AcctNumb1 != 'ADMIN' here, you want an exact match, which won't have the percent sign at the end for sure. Or you want to use LIKE here, too.

Author Closing Comment

ID: 39538473
I sure tried to make it more complicated than necessary.


Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question