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.

PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...