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
Solved

SQL CASE in WHERE clause

Posted on 2014-01-22
7
278 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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.​
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

840 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