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
Tuesday, 7 January 2020
Multiple Search Item Depending Upon Search Parameters in SQL Stored Procedure
Subscribe to:
Posts (Atom)