How to use If else in where clause of sql query

Question

Below is my stored procedure.

USE [CBRDev]
GO
/****** Object:  StoredProcedure [dbo].[usp_ExportData]    Script Date: 6/14/2017 12:48:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_ExportData]
(
@StartDate Date,
@EndDate Date
)

AS
BEGIN
 Select REPLACE(U.EmployeeID, ',','') as EmployeeID,REPLACE(U.ClientID, ',','') as ClientID,REPLACE(U.ID, ',','') as ID,
 REPLACE(U.FirstName, ',','')as FirstName,REPLACE(U.MiddleName, ',','')as MiddleName,
 REPLACE(U.LastName, ',','')as LastName,REPLACE(U.Email, ',','')as Email,REPLACE(U.SSN, ',','')as SSN,
 REPLACE(U.DateOfBirth, ',','')as DateOfBirth,REPLACE(U.Gender, ',','')as Gender,REPLACE(U.CreatedDate, ',','')as CreatedDate,
REPLACE(U.ModifiedDate, ',','')as ModifiedDate, REPLACE(UPI.StreetAddress1, ',','')as StreetAddress1,
REPLACE(UPI.StreetAddress2, ',','')as StreetAddress2,REPLACE(UPI.City, ',','')as City,
REPLACE(UPI.State, ',','')as State,
REPLACE(UPI.ZipCode, ',','')as ZipCode,
REPLACE(UPI.CellPhoneNumber, ',','')as CellPhoneNumber, '' as Department, '' as JobTitle, '' as StreetAddress3 from Users U INNER JOIN PersonalContacts UPI ON U.ID= UPI.UserID
 where U.ModifiedDate >=@StartDate and U.ModifiedDate<= @EndDate
END

In which if @StartDate and @EndDate parameter is null then I want to remove where condition.

and if @StartDate and @EndDate parameter is present then apply where condition then How can I do that?

Answer 1

I Just check before calling stored procedure whether 'TEMP_DATA_TABLE' is null or not if it is not null then drop that table. And it works.

USE [CBRDev]    
GO  
/****** Object:  StoredProcedure [dbo].[usp_ExportData] Script Date: 
6/14/2017 12:48:44 PM ******/
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
ALTER procedure [dbo].[usp_ExportData]  
(
   @StartDate Date,
   @EndDate Date  
)

AS
BEGIN

if (select OBJECT_ID('TEMP_DATA_TABLE')) Is Not Null
Begin 
DROP Table TEMP_DATA_TABLE
end
Select REPLACE(U.EmployeeID, ',','') as EmployeeID,
REPLACE(U.ClientID, ',','') as ClientID, 
REPLACE(U.ID, ',','') as ID, REPLACE(U.FirstName, ',','')as FirstName, 
REPLACE(U.MiddleName, ',','')as MiddleName,
REPLACE(U.LastName, ',','')as LastName,
REPLACE(U.Email, ',','')as Email,REPLACE(U.SSN, ',','')as SSN,
REPLACE(U.DateOfBirth, ',','')as DateOfBirth,
REPLACE(U.Gender, ',','')as Gender,
REPLACE(U.CreatedDate, ',','')as CreatedDate,
REPLACE(U.ModifiedDate, ',','')as ModifiedDate, 
REPLACE(UPI.StreetAddress1, ',','')as StreetAddress1,
REPLACE(UPI.StreetAddress2, ',','')as StreetAddress2,
REPLACE(UPI.City, ',','')as City,
REPLACE(UPI.State, ',','')as State,
REPLACE(UPI.ZipCode, ',','')as ZipCode,
REPLACE(UPI.CellPhoneNumber, ',','')as CellPhoneNumber, '' as Department, '' 
as JobTitle, '' as StreetAddress3   
INTO TEMP_DATA_TABLE   
FROM Users U INNER JOIN PersonalContacts UPI ON U.ID= UPI.UserID  

IF (@StartDate IS NOT NULL AND @EndDate IS NOT NULL)  
  SELECT * FROM TEMP_DATA_TABLE 
  where ModifiedDate >=@StartDate and ModifiedDate<= @EndDate   
ELSE  
  SELECT * FROM TEMP_DATA_TABLE  
END

Answer 2

USE [CBRDev]    
GO  
/****** Object:  StoredProcedure [dbo].[usp_ExportData] Script Date: 
6/14/2017 12:48:44 PM ******/
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
ALTER procedure [dbo].[usp_ExportData]  
(
   @StartDate Date,
   @EndDate Date  
)

AS
BEGIN

Select REPLACE(U.EmployeeID, ',','') as EmployeeID,
REPLACE(U.ClientID, ',','') as ClientID, 
REPLACE(U.ID, ',','') as ID, REPLACE(U.FirstName, ',','')as FirstName, 
REPLACE(U.MiddleName, ',','')as MiddleName,
REPLACE(U.LastName, ',','')as LastName,
REPLACE(U.Email, ',','')as Email,REPLACE(U.SSN, ',','')as SSN,
REPLACE(U.DateOfBirth, ',','')as DateOfBirth,
REPLACE(U.Gender, ',','')as Gender,
REPLACE(U.CreatedDate, ',','')as CreatedDate,
REPLACE(U.ModifiedDate, ',','')as ModifiedDate, 
REPLACE(UPI.StreetAddress1, ',','')as StreetAddress1,
REPLACE(UPI.StreetAddress2, ',','')as StreetAddress2,
REPLACE(UPI.City, ',','')as City,
REPLACE(UPI.State, ',','')as State,
REPLACE(UPI.ZipCode, ',','')as ZipCode,
REPLACE(UPI.CellPhoneNumber, ',','')as CellPhoneNumber, '' as Department, '' 
as JobTitle, '' as StreetAddress3   
INTO #NEW_TABLE   
FROM Users U INNER JOIN PersonalContacts UPI ON U.ID= UPI.UserID  

IF (@StartDate IS NOT NULL AND @EndDate IS NOT NULL)  
  SELECT * FROM #NEW_TABLE  
  where ModifiedDate >=@StartDate and ModifiedDate<= @EndDate   
ELSE  
  SELECT * FROM #NEW_TABLE   
END

Answer 3

You can just modify your WHERE condition:

where @StartDate IS NULL OR @EndDate IS NULL OR 
      U.ModifiedDate BETWEEN @StartDate AND @EndDate

To prevent parameter sniffing (one cached execution plan used for different parameter values), I suggest that you use recompile hint on procedure (WITH RECOMPILE) or query level. OPTION (RECOMPILE);.

Answer 4

try this....

ALTER procedure [dbo].[usp_ExportData]
(
@StartDate Date,
@EndDate Date
)
AS
BEGIN
declare @sql varchar(max)
set @sql = ''
set @sql += ' Select REPLACE(U.EmployeeID, '','','') as 
EmployeeID,REPLACE(U.ClientID, '','','') as ClientID,REPLACE(U.ID, '','','') as ID,
REPLACE(U.FirstName, '','','')as FirstName,REPLACE(U.MiddleName, '','','')as MiddleName,
REPLACE(U.LastName, '','','')as LastName,REPLACE(U.Email, ',','')as 
Email,REPLACE(U.SSN, '','','')as SSN,
REPLACE(U.DateOfBirth, '','','')as DateOfBirth,REPLACE(U.Gender, '','','')as
Gender,REPLACE(U.CreatedDate, '','','')as CreatedDate,
REPLACE(U.ModifiedDate, '','','')as ModifiedDate, REPLACE(UPI.StreetAddress1,
 '','','')as StreetAddress1,
REPLACE(UPI.StreetAddress2, '','','')as StreetAddress2,REPLACE(UPI.City, '','','')as
 City,
REPLACE(UPI.State, '','','')as State,
REPLACE(UPI.ZipCode, '','','')as ZipCode,
REPLACE(UPI.CellPhoneNumber, '','','')as CellPhoneNumber, '' as Department, '' as 
JobTitle, '' as StreetAddress3 from Users U INNER JOIN PersonalContacts UPI ON
 U.ID= UPI.UserID'

if(@StartDate <> '' or @EndDate <> '')set @sql += ' where U.ModifiedDate >='''+@StartDate+''' and U.ModifiedDate<= '''+@EndDate+''''

exec(@sql)
END

Answer 5

ALTER procedure [dbo].[usp_ExportData]
(
@StartDate Date,
@EndDate Date
)
AS
BEGIN
declare @sql varchar(max)
set @sql = ''
set @sql += 'Select REPLACE(U.EmployeeID, ',','') as 
EmployeeID,REPLACE(U.ClientID, ',','') as ClientID,REPLACE(U.ID, ',','') as ID,
REPLACE(U.FirstName, ',','')as FirstName,REPLACE(U.MiddleName, ',','')as MiddleName,
REPLACE(U.LastName, ',','')as LastName,REPLACE(U.Email, ',','')as 
Email,REPLACE(U.SSN, ',','')as SSN,
REPLACE(U.DateOfBirth, ',','')as DateOfBirth,REPLACE(U.Gender, ',','')as
Gender,REPLACE(U.CreatedDate, ',','')as CreatedDate,
REPLACE(U.ModifiedDate, ',','')as ModifiedDate, REPLACE(UPI.StreetAddress1,
 ',','')as StreetAddress1,
REPLACE(UPI.StreetAddress2, ',','')as StreetAddress2,REPLACE(UPI.City, ',','')as
 City,
REPLACE(UPI.State, ',','')as State,
REPLACE(UPI.ZipCode, ',','')as ZipCode,
REPLACE(UPI.CellPhoneNumber, ',','')as CellPhoneNumber, '' as Department, '' as 
JobTitle, '' as StreetAddress3 from Users U INNER JOIN PersonalContacts UPI ON
 U.ID= UPI.UserID'
 if(@StartDate and @EndDate <> '')set @sql += 'where U.ModifiedDate
 >=''+@StartDate+'' and U.ModifiedDate<= ''+@EndDate'''
exec(@sql)
END