Solved

SQL CASE in WHERE clause

Posted on 2014-01-22
7
277 Views
Last Modified: 2014-01-27
When it gets to the where it busts out in error


what am i doing wrong




create spsomthing

	(@num varchar(20)=NULL
	,@LastName varchar(30)=NULL
	,@FirstName varchar(25)=NULL
)     AS

 declare @searchint as int
 	SELECT @searchint =
	case when NOT @num IS NULL then 1
	when NOT @name  IS NULL then 2
       else 0 END 

 SELECT DISTINCT  *
	FROM MYTABLE
WHERE	
		
CASE @searchint
	WHEN 1 THEN (  @num = MYTABLE.Num)
	WHEN 2 THEN (  @LastName = MYTABLE.Last OR  @FirstName = MYTABLE.First )
END

Open in new window

0
Comment
Question by:Ess Kay
  • 4
  • 2
7 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 39799955
Give this a whirl...
SELECT DISTINCT  *
FROM MYTABLE
WHERE	
   ( @searchint= 1 AND @num = MYTABLE.Num) OR 
   ( @searchint= 2 AND (@LastName = MYTABLE.Last OR @FirstName = MYTABLE.First ))

Open in new window

I have an article out there on SQL Server CASE Solutions, but unfortunately it includes a love note to myself that I need to add a section on CASE in WHERE blocks.
0
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 250 total points
ID: 39799965
in your line 1 -->
you have to mention that you are creating a procedure

so it should be as

CREATE PROCEDURE spsomthing

the below select statement should be as

declare @searchint as int
 	SELECT @searchint =
	case when @num IS NOT  NULL then 1
	when NOT @name  IS NOT NULL then 2
       else 0 END 

Open in new window


There should be no gap (no blank lines in any sql statements)

 SELECT DISTINCT  *
	FROM MYTABLE
WHERE	
(@num = MYTABLE.Num AND @searchint = 1)
OR 
(( @LastName = MYTABLE.Last OR  @FirstName = MYTABLE.First) AND @searchint = 2)

Open in new window


so your whole code should look like below

CREATE PROCEDURE spsomthing
(

	(@num varchar(20)=NULL
	,@LastName varchar(30)=NULL
	,@FirstName varchar(25)=NULL
)     AS
BEGIN
 declare @searchint as int
 	SELECT @searchint =
	case when @num IS NOT  NULL then 1
	when NOT @name  IS NOT NULL then 2
       else 0 END 

 SELECT DISTINCT  *
	FROM MYTABLE
WHERE	
(@num = MYTABLE.Num AND @searchint = 1)
OR 
(( @LastName = MYTABLE.Last OR  @FirstName = MYTABLE.First) AND @searchint = 2)

END

Open in new window

0
 
LVL 15

Author Comment

by:Ess Kay
ID: 39800010
i was hoping to use a  CASE or IF in the where

is that possible
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 39800030
>i was hoping to use a  CASE or IF in the where ... is that possible
Possible, probably, but it forces SQL to evaluate every expression for every row, which will affect speed.  Also imo using boolean AND and OR's is easier to read.  

This article may help, and pretty much says the same thing.
http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx
0
 
LVL 15

Author Comment

by:Ess Kay
ID: 39800081
the thing is, the SP i am correcting has over 800 lines of code


currently looks like this

example
the case, or if seems to be much cleaner. Also, with the 45 or so different outcomes, once it hits the correct one, it wont have to evaluate the others
0
 
LVL 15

Assisted Solution

by:Ess Kay
Ess Kay earned 0 total points
ID: 39800131
an alternative i found is here stackoverflow.com/questions/19090870/building-dynamic-where-condition-in-sql-statement

but i was hoping to have a more practical solution





You cannot simply put your variable in normal SQL as you have in this line:

select * from table_name where @where;
You need to use dynamic SQL. So you might have something like:

DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Table_Name WHERE 1 = 1 ';
DECLARE @Params NVARCHAR(MAX) = '';

IF @Vendor_Name IS NOT NULL
    BEGIN
        SET @SQL += ' AND UPPER(vendors.VENDOR_NAME) LIKE ''%'' + UPPER(@VendorNameParam) + ''%''';
    END
ELSE IF @Entity IS NOT NULL
    BEGIN
        SET @SQL += ' AND headers.ORG_ID = @EntityParam';
    END;

EXECUTE SP_EXECUTESQL @SQL, N'@VendorNameParam VARCHAR(50), @EntityParam INT',
                    @VendorNameParam = @Vendor_Name, @EntityParam = @Entity;
0
 
LVL 15

Author Closing Comment

by:Ess Kay
ID: 39811506
i used the first one, made 800 lines of code into 100


thanks
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

777 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