Star79
asked on
Case Statement in Where Clause on a Parameter
Hello I have the below store proc
Iam trying to put a case condition on the param start date to check to see if its empty string or the user has passed a value.Iam getting an incorrect syntax on my code.Please help.
declare @po_start_date varchar(15)
declare @po_end_date varchar(15)
SELECT
ex.vendor_code,
ex.program,
ex.Po_num,
ex.date_create,
ex.Printed_Date,
ex.status,
ex.po_item_No,
ex.Supplier_pn,
ex.price,
ex.unit_of_meas,
ex.extd_cost,
ex.quantity,
ex.open_qty,
ex.baseline_date,
ex.[Current_Date],
ex.nomenclature,
ex.customer_no,
ex.control_and_item,
ex.need_date,
ex.fax,
ex.state,
ex.vendor_name,
ex.vendor_phone,
ex.vendor_contact,
ex.disposition,
ex.ship_via,
ex.need_date3,
ex.a_status,
ex.data,
ex.next_higher_assy
FROM
expeditetbl1 ex
left join
(
select
emp_num, pGM_CODES
from exp_dummytbl
group by emp_num, pGM_CODES
)
Q ON ex.program = Q.pGM_CODES
where Q.emp_num='092'
and ex.vendor_code IS NOT null
and ex.open_qty <> 0
and (ex.program ='632'
and Case
When @po_start_date <> ''
Then (ex.[Current_Date] > = @po_start_date)
END
Iam trying to put a case condition on the param start date to check to see if its empty string or the user has passed a value.Iam getting an incorrect syntax on my code.Please help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Alternatively to Jim's solution..
My guess is that the OP only wants to make the parameter optional.
Tip: if the @po_start_dateparameter is null then only the > applies, else only the equality applies and annuls the condition predicate.
Hope this helps.
My guess is that the OP only wants to make the parameter optional.
WHERE
.....
ex.[Current_Date] > = isnull(@po_start_date, ex.[Current_Date] )
Tip: if the @po_start_dateparameter is null then only the > applies, else only the equality applies and annuls the condition predicate.
Hope this helps.
here it is
where ex.[Current_Date] >= isnull(@po_start_date, ex.[Current_Date])
oops, it is already posted :)
where ex.[Current_Date] >= isnull(@po_start_date, ex.[Current_Date])
oops, it is already posted :)
Nope, that ISNULL() approach involving a column can be awful for performance. You're forcing the column value to be compared to itself for every row.
The better coding approach is:
and (ISNULL(@po_start_date, '') = '' OR @po_start_date = ex.[Current_Date])
because SQL can, and very often will, 'short-circuit' before the last comparison.
For example, try the code below against any table with a "name" column, and look at the query plans. For the first plan, you won't (shouldn't) see a 'Predicate'/comparison at all. With the second, you'll see that it does need a Predicate/comparison.
declare @name varchar(128)
--no predicate, because SQL can strip it.
select *
from dbo.table_with_name_column
where isnull(@name, '') = '' or name = @name --proper method
--with predicate, because SQL can't strip it.
select *
from dbo.table_with_name_column
where name = isnull(@name, name) --improper method, do not use!
The better coding approach is:
and (ISNULL(@po_start_date, '') = '' OR @po_start_date = ex.[Current_Date])
because SQL can, and very often will, 'short-circuit' before the last comparison.
For example, try the code below against any table with a "name" column, and look at the query plans. For the first plan, you won't (shouldn't) see a 'Predicate'/comparison at all. With the second, you'll see that it does need a Predicate/comparison.
declare @name varchar(128)
--no predicate, because SQL can strip it.
select *
from dbo.table_with_name_column
where isnull(@name, '') = '' or name = @name --proper method
--with predicate, because SQL can't strip it.
select *
from dbo.table_with_name_column
where name = isnull(@name, name) --improper method, do not use!
agree with ScottPletcher
and (ISNULL(@po_start_date, '') = '' OR @po_start_date = ex.[Current_Date])
or this one
and (ISNULL(@po_start_date, '') = '' OR @po_start_date = ex.[Current_Date])
^^^ looks best option, which I always used in my old asp portals :) after getting values from a search form on a webpage, I used one query like
sql="select ...
where ...
((':param1'='') or (column1=':param1')) and
((':param2'='') or (column2=':param2')) and
...
((':paramN'='') or (columnN=':paramN'))"
then
sql.replace(":param1",para m1)
sql.replace(":param2",para m2)
...
sql.replace(":paramN",para mN)
then run sql in db...
and (ISNULL(@po_start_date, '') = '' OR @po_start_date = ex.[Current_Date])
or this one
and (ISNULL(@po_start_date, '') = '' OR @po_start_date = ex.[Current_Date])
^^^ looks best option, which I always used in my old asp portals :) after getting values from a search form on a webpage, I used one query like
sql="select ...
where ...
((':param1'='') or (column1=':param1')) and
((':param2'='') or (column2=':param2')) and
...
((':paramN'='') or (columnN=':paramN'))"
then
sql.replace(":param1",para
sql.replace(":param2",para
...
sql.replace(":paramN",para
then run sql in db...
Secondly, you can't put a command within a CASE statement. This:
Open in new window
... is not allowed.Just guessing, but perhaps your line could read something like:
Open in new window
CASE only renders a value. It cannot take action. If you require a @po_start_date, you'll need to validate that at the top of your script (or your proc, if that is what this will eventually become).