CODEWITHSUNDEEP
sql-serverstored-procedures
Srikanth Reddy
Srikanth Reddy

Reputation: 545

Multiple conditions on a where clause column in SQL Server

I had written a stored procedure where it needs to execute the where condition based on the parameter values as follows :

alter proc sp_cc_get_grn_details
(
@vendor_id  varchar(100) = null,
@po_id int = null,
@invoice_id  int = null,
@invoice_from_dt datetime2 = null,
@invoice_to_dt datetime2 = null,
@grn_status int = null

)
AS
BEGIN

SELECT vd.vendor_id,vd.vendor_name,id.po_id,id.invoice_id,id.invoice_amount,id.invoice_date, 
CASE
    WHEN id.grn_status = 0 THEN 'Pending'
    WHEN id.grn_status = 1 THEN 'Completed'
    ELSE 'Pending'
END AS grn_status into #Tempres
FROM 
vendor_details vd JOIN po_details pd on vd.vendor_id = pd.vendor_id
join invoice_details id on id.po_id = pd.po_id 
WHERE 
(@vendor_id is null or @vendor_id = '' or vd.vendor_id = @vendor_id) AND
(@po_id is null or @po_id = '' or id.po_id = @po_id) AND
(@invoice_id is null or @invoice_id = '' or id.invoice_id = @invoice_id) and 
(id.invoice_date BETWEEN @invoice_from_dt AND @invoice_to_dt) 


if(@grn_status = 1)
    select *  from #Tempres where grn_status = 'Completed';
else if(@grn_status = 2)
    select * from #Tempres where grn_status = 'Pending';
else
    select * from #Tempres;

drop table #Tempres;
END

The above procedure is working. But what's my need is, Is there any way to make "@grn_status" parameter too in the where clause instead of writing with if & else.

Upvotes: 0

Views: 473

Answers (1)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30545

something like this would work

where @grn_Status not in (1, 2) or 
 grn_status = 
   case @grn_status 
   when 1 then 'Completed' 
   when 2 then 'Pending'
   end

Upvotes: 1

Related Questions