Sql-server – Case statement in WHERE clause in SQL Server

sql serverstored-procedures

I am new to SQL Server
i am writing stored procedure for report search

CREATE PROCEDURE [dbo].[SP_BILLSEARCH](                                
 @CustCode INT,                    
 @BillAmount MONEY,                    
 @Email VARCHAR(50),              
 @Phone VARCHAR(50),    
 @BillDate DATE
 )
 AS                                  
BEGIN 
   SELECT TOP 200 
     Name
     ,CASE WHEN [BillType] ='CREDIT' 
          THEN ISNULL(CAST(creditDate AS VARCHAR(20)), '')
          ELSE ISNULL(CAST(billdate  AS VARCHAR(20)), '')
          END AS BillDate     
     ,CustomerCode
     ,BillAmount
     ,Email
     ,Phone
FROM BillEntry
WHERE
CustomerCode LIKE '%' + @CustCode + '%'  --Here convert int to varchar error  
AND BillAmount LIKE '%' + @BillAmount + '%'  --Here convert money error  
AND Email LIKE '%' + @Email + '%'   
AND Phone LIKE '%' + @Phone + '%'   
--AND @BillDate LIKE '%' + @BillDate + '%'
--Here How to set condition if BillType = 'CREDIT then the date column is  creditDate
--otherwise date column is billdate
END

The report can filter by custcode, billamount, email, phone and billdate

Best Answer

As pointed out, your query is likely to perform badly or not at all. You are mixing datatypes and using LIKE in places that it doesn't make sense.

But to answer your question on how to have a conditional where clause (again, this tends to perform badly), you can use.

AND CASE WHEN BillType = 'CREDIT' THEN creditDate ELSE billDate END = @BillDate

You should use LIKE only against string (CHAR, NCHAR, VARCHAR, NVARCHAR) columns and predicates. Additionally, only if you need to ignore case or use wildcard searches. However, leading wildcard searches can't use an index.

If you are having the problem of creditDate being a date/time and the passed in variable of @BillDate is just a date then you can use the following.

AND creditDate >= @BillDate AND creditDate < DATEADD(DAY, 1, @BillDate)

But you can't use LIKE with numbers, money or date; or at least not directly. You would have to convert them to strings first, which would again not use indexes and perform poorly.

If you posted the create table statement and what you were attempting to achieve with the LIKE statements, we could assist more.