Solved

SQL CASE in WHERE clause

Posted on 2014-01-22
7
281 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 66

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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 66

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

632 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