SImple query help

[kingstonr]

HI,
if I pass fname then query should return only that condition,If null it should return all records.

declare
@fname as nvarchar(10)
set @fname=null
select * from emp where fname=@fname or @fname=null(Expected It will return all records but it returns 0 records)

I tried another approach

declare
@fname as nvarchar(10)
set @fname=''
select * from emp where fname=@fname or @fname=''(It works but it takes lot of time for the query to complete)
In my actual query I have joins.

Kindly Guide me with any other good approach for this type of conditions.

 

[gmmastros]

You cannot test a value to be equal to null. You need to test for Is null.

[COLOR=blue]Declare[/color] @fname [COLOR=blue]as[/color] nvarchar(10)
[COLOR=blue]set[/color] @fname=null

[COLOR=blue]select[/color] * 
[COLOR=blue]from[/color]   emp 
[COLOR=blue]where[/color]  fname=@fname or @fname [!]Is[/!] null

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

I should mention that if there are multiple conditions in your actual query, then you need to use parenthesis to get the correct output, like this...

[COLOR=blue]Declare[/color] @fname [COLOR=blue]as[/color] nvarchar(10)
[COLOR=blue]set[/color] @fname=null

[COLOR=blue]select[/color] * 
[COLOR=blue]from[/color]   emp 
[COLOR=blue]where[/color]  SomeCodition = @someValue
       And (fname=@fname or @fname [COLOR=blue]Is[/color] null)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom