s

mssql select all if the where clause parameter is null or empty edit button Edit

author
Murugan Andezuthu Dharmaratnam | calendar 23 October 2020 | 1611

In an MSSQL stored procedure, I am passing the Name parameter how to select all if the name parameter in the where clause is null or empty. In the below-stored procedure, I am handling the null or empty value passed to the stored procedure.

SQL Script

CREATE PROCEDURE [dbo].[sp_spname]
	@Name nvarchar(64)
AS
BEGIN
BEGIN TRANSACTION
    SET NOCOUNT ON;

	select * from Customer
	where 1 = 1 
	AND COALESCE(Customer.Name,'') like '%' IIF(ISNULL(@Name, '') = '', ISNULL(Customer.Name, ''), @Name) '%'
              
COMMIT
END
GO