Tuesday 7 January 2020

Multiple Search Item Depending Upon Search Parameters in SQL Stored Procedure

CREATE PROCEDURE dbo.usp_ProcName
 @Name          VARCHAR(100) 
,@FirstName     VARCHAR(100) 
,@City          VARCHAR(100) 
,@Birthday      VARCHAR(100) 
AS 
BEGIN
  SET NOCOUNT ON;

 DECLARE @sql NVARCHAR(MAX);

SET @sql = N'  select  id
                       ,name
                       ,firstname
                       ,city
                       ,birthday
                from dbo.Customers  WHERE 1 = 1'
       + CASE WHEN @Name IS NOT NULL 
              THEN N' AND Name = @Name' ELSE N' ' END 
       + CASE WHEN @FirstName IS NOT NULL 
              THEN N' AND FirstName = @FirstName' ELSE N' ' END 
       + CASE WHEN @City IS NOT NULL 
              THEN N' AND City = @City' ELSE N' ' END 
       + CASE WHEN @Birthday IS NOT NULL 
              THEN N' AND BirthDay LIKE ''%'' + @Birthday + ''%''' ELSE N' ' END 

EXECUTE sp_executesql @Sql
                    ,N'@Name VARCHAR(100) , @FirstName VARCHAR(100) 
                       @City VARCHAR(100), @BirthDay VARCHAR(100)'
                    ,@Name
                    ,@FirstName
                    ,@City
                    ,@Birthday 
END