CODEWITHSUNDEEP
sql-serversql-server-2008
user2977985
user2977985

Reputation:

Using case statement getting error as incorrect syntax

In store procedure I am placing code as 

SELECT *
FROM [Order] O 
INNER JOIN Users U ON O.UserId=U.UserId
INNER JOIN State S ON S.StateId=O.Billing_StateId
INNER JOIN OrderStatus OS ON O.OrderStatusId=OS.OrderStatusId
WHERE (CASE WHEN ISNULL(@Name,'')='' THEN 0=0  ELSE O.Billing_FirstName LIKE '%' + @Name + '%' OR O.Billing_LastName LIKE'%' + @Name + '%' END)
AND (CASE WHEN ISNULL(@Email,'')='' THEN 0=0  ELSE O.Billing_Email LIKE '%' + @Email + '%' END)

but it is giving error as Incorrect syntax near =

I dont know what I am doing wrong in placing THEN 0=0 ?

If it is wrong what should I wrote over there and is my query ok?

If @Name parameter is not there then it will not check 1st condition and will check for next condition

Upvotes: 0

Views: 359

Answers (3)

ViKiNG
ViKiNG

Reputation: 1334

Try this:

SELECT *
FROM [Order] O 
INNER JOIN Users U ON O.UserId=U.UserId
INNER JOIN State S ON S.StateId=O.Billing_StateId
INNER JOIN OrderStatus OS ON O.OrderStatusId=OS.OrderStatusId
WHERE 
(NULLIF(@Name,'') IS NULL OR O.Billing_FirstName LIKE '%' + @Name + '%' OR O.Billing_LastName LIKE'%' + @Name + '%')
AND (NULLIF(@Email,'') IS NULL OR O.Billing_Email LIKE '%' + @Email + '%')

Upvotes: 0

Tien Nguyen Ngoc
Tien Nguyen Ngoc

Reputation: 1555

You can try this.

WHERE (@Name IS NULL OR( O.Billing_FirstName LIKE '%' + @Name + '%' OR O.Billing_LastName LIKE'%' + @Name + '%' ))
AND (@Email IS NULL OR ( O.Billing_Email LIKE '%' + @Email + '%'))

Upvotes: 3

Denny Sutedja
Denny Sutedja

Reputation: 538

your CASE statement is wrong

in case condition you must define new parameter that will replace the condition.

see this (i dont know if its work or not)

WHERE
(CASE 
    WHEN ISNULL(@Name,'')='' THEN 0
    WHEN O.Billing_FirstName LIKE '%' + @Name + '%' OR O.Billing_LastName LIKE'%' + @Name + '%' THEN 1
 END as cond1) = 0 (*or 1, i dont know what you need)
 AND ........

Upvotes: -1

Related Questions