Use coalesce or datalength rather appending query and executing it

1)coalesce

declare @FirstName nvarchar(30)
declare @LastName nVarchar(30)
set @FirstName = 'Nishant'
set @LastName = null
select * from employee
where FirstName = @FirstName
and LastName = coalesce(@LastName,LastName)

what this does if you pass @LastName null then it will compare column LastName = LastName,
and if you pass @LastName then it will apply and where conditions. Same as this there is one more way out for not using old way to executing dynamic SQLs.


2)datalength

declare @FirstName nvarchar(30)
declare @LastName nVarchar(30)
set @FirstName = 'Nishant'
set @LastName = null
select * from employee
where FirstName = @FirstName
and LastName = case when datalength(@LastName) > 0 then @LastName else LastName end

This is just one simple logic where I have check datalenght of @LastName, which will return 0 if the @LastName is null else it would anyway greater than 0. So by using case when you can do same thing which we did using coalesce function.

isn't it great to work smart than work hard :)