Solved

SQL CASE in WHERE clause

Posted on 2014-01-22
7
279 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

679 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