Pass Null Parameter for UniqueIdentfier Datatype in SQL

Question

Declare @ID uniqueidentifier =Null

select * from Emp where EmpID=@ID

Above query giving no results.

Answer 1

NULL is not a value, is a state, so you can't test for something equals to NULL using standard operators.

All expression in which is involved a NULL will result in NULL

DECLARE @I INT = NULL
DECLARE @A VARCHAR(20) = NULL

'Hello ' + @A + 'World' = NULL
127 * 54 - (32 / @i) = NULL

So your query select * from Emp where EmpID=@ID will give no results when @ID is null

You need to use the special operator IS NULL to test for your parameter (see @Sergey comment)

select * 
from Emp 
where EmpID=@ID  /* this will catch rows when @ID is not NULL */
or (EmpID IS NULL and @ID IS NULL) /* this will catch rows when @ID is NULL */

Answer 2

Handle it using the ternery operator like this

Declare @ID nvarchar(50);
select * from Emp where EmpID=@ID is null?0:@ID;