Nov 16, 2016

If parameter is null then select all data in SQL Server Query

We have a scenario, suppose we have a table like below


CREATE TABLE EMP
(
       FNAME VARCHAR(100)
       ,LNAME VARCHAR(100)
       ,CREATEDDATE DATETIME,
)

INSERT INTO EMP
SELECT 'DILIP','SINGH',GETDATE()
UNION ALL
SELECT 'ANIL','SINGH',GETDATE()
UNION ALL
SELECT 'ASHISH','PRATAP', GETDATE()


See following SQL statements


DECLARE @myName VARCHAR(20)='ANIL'
SELECT FNAME LNAME FROM EMP
WHERE FNAME= @myName



Here @myName parameter is declared and condition are if @myName =Null then return all records otherwise return related records.

Please see following query which can resolve this problem


DECLARE @myName VARCHAR(20)=NUll
SELECT FNAME LNAME FROM EMP
WHERE FNAME=ISNULL(@myName,FNAME)


We can use ISNULL function which checked if parameter @myName is Null then return FNAME column.