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 the following SQL statements.


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



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

Please see the 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 the parameter @myName is Null then return FNAME column.

Related Posts

What is the Use of isNaN Function in JavaScript? A Comprehensive Explanation for Effective Input Validation

In the world of JavaScript, input validation is a critical aspect of ensuring that user-provided data is processed correctly. One indispensa...