Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

SQL CASE in WHERE clause

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
Ess Kay
Asked:
Ess Kay
  • 4
  • 2
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Surendra NathCommented:
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
 
Ess KayEntrapenuerAuthor Commented:
i was hoping to use a  CASE or IF in the where

is that possible
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Ess KayEntrapenuerAuthor Commented:
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
 
Ess KayEntrapenuerAuthor Commented:
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
 
Ess KayEntrapenuerAuthor Commented:
i used the first one, made 800 lines of code into 100


thanks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now