CODEWITHSUNDEEP
sqlsql-serverstored-procedures
mohamed faisal
mohamed faisal

Reputation: 446

How to make a where clause is optional in Sql Server?

I have a situation like user will search the records, they will give the conditions in where clause. So they may give conditions or may not. So in this criteria How to make a query.

For example, User may search all records. Some times they may search with date between. So how to make a query the column field of Date is optional.

This is my query

select  transactions.storeid as StoreID, MONTH(transactions.Time) Month, 
        transactionsEntry.TransactionNumber,transactionsEntry.Quantity,
        items.ItemLookupCode,items.DepartmentID,items.CategoryID,items.SubDescription1,
        suppliers.SupplierName,suppliers.Code
FROM        [HQMatajer].[dbo].[Transaction] as transactions
RIGHT JOIN  [HQMatajer].[dbo].[TransactionEntry] as transactionsEntry
ON transactions.TransactionNumber=transactionsEntry.TransactionNumber 

INNER JOIN  [HQMatajer].[dbo].[Item] as items
ON transactionsEntry.ItemID=items.ID

INNER JOIN  [HQMatajer].[dbo].[Supplier] as suppliers
ON items.SupplierID=suppliers.ID

where 
    YEAR(transactions.Time)=2015 and transactions.TransactionNumber='5526499'  
    and items.ItemLookupCode='806077P0001' and transactions.StoreID='2001'

Here, user may search year=2015 or they may not search. So how can i handle this.

Upvotes: 1

Views: 281

Answers (1)

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

Pass NULL for the date parameters when you don't to search accroding to year and in WHERE clause add an OR condition with @Year IS NULL

declare @year INT=2015

select  transactions.storeid as StoreID, MONTH(transactions.Time) Month, 
        transactionsEntry.TransactionNumber,transactionsEntry.Quantity,
        items.ItemLookupCode,items.DepartmentID,items.CategoryID,items.SubDescription1,
        suppliers.SupplierName,suppliers.Code
FROM        [HQMatajer].[dbo].[Transaction] as transactions
RIGHT JOIN  [HQMatajer].[dbo].[TransactionEntry] as transactionsEntry
ON transactions.TransactionNumber=transactionsEntry.TransactionNumber 

INNER JOIN  [HQMatajer].[dbo].[Item] as items
ON transactionsEntry.ItemID=items.ID

INNER JOIN  [HQMatajer].[dbo].[Supplier] as suppliers
ON items.SupplierID=suppliers.ID

where 
    (YEAR(transactions.Time)=@year OR ISNULL(@year,'')='')  and transactions.TransactionNumber='5526499'  
    and items.ItemLookupCode='806077P0001' and transactions.StoreID='2001'

If the year is not hard coded, pass it to @year variable and use in the query.

Update:

In case you want to compare it with comma seperated string,declare @year as varchar(100) like

declare @year INT=2015 to declare @year VARCHAR(100)='2014,2015'

and then change the where condition like:

 (CHARINDEX(','+CAST(YEAR(transactions.Time) as VARCHAR(4))+',',',' + @year + ',')>0 
   OR ISNULL(@year,'')='')  
   and transactions.TransactionNumber='5526499'  
   and items.ItemLookupCode='806077P0001' and transactions.StoreID='2001'

The above query will check if the year of transactions.Time is present in the @year or not and display the results accordingly.

Note: Specify the size of varchar according to your needs, that will result in better performance.

Upvotes: 2

Related Questions