s

Home > mssql > mssql select all if the where clause parameter is null or empty

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

author
Murugan Andezuthu Dharmaratnam | calendar 23 October 2020 | 1977

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