To Select Row By Id  or All Rows from Table using SQL Server COALESCE() Function.


Hi All,

This Post is about how to use SQL Server COALESCE() function to select Row by Id or All row . Consider following example


SELECT UserId,UserName,UserAddress FROM UserMaster WHERE UserId=coalesce('1,UserId);

In Above example result will be user details whose UserId is 1
---------------------------------------------------------------------------------------------------

Now Consider another example

SELECT UserId,UserName,UserAddress FROM UserMaster WHERE UserId=coalesce(null,UserId);

In Above example  i have passed value ''NULL'  to UserId in COALESCE() function, means when perticular Id is passed to COALESCE() it will return record matching Userid=1 and if null is passes then it will return All Rows. But Remember UserId is a Primary Key.

------------------------------------------------------------------------------------------------------
Now have look at follwing example with stored procedure

CREATE PROCEDURE  msp_select_user
@UserId  Bigint
AS
BEGIN
IF @UserId=0
SET @UserId=Null
END
BEGIN
SELECT UserId,UserName,UserAddress FROM UserMaster WHERE UserId=coalesce(@UserId,UserId);
END


Enjoy SQLing.....