Need help in where clause condition

Question

I have a stored procedure which is similar to below code :

Create  PROCEDURE Proc1(

@empid INT,

@date DATETIME            
)
AS
BEGIN
SELECT * FROM tbl_emp

WHERE

empid=@empid

and date = @date

END

now I have a case where I will send only one paremeter out of the two and need the data considering only the sent parameter in that case.

can someone help.

Regards,

Anil.

Answer 1

where I will send only one paremeter out of the two and

Hello Anil,

Not send = NULL? Then check additional if the parameter(s) is null

Create  PROCEDURE Proc1(
    @empid INT,
    @date DATETIME             
)
 AS
 BEGIN
 
    SELECT * FROM tbl_emp 
    WHERE (empid=@empid OR @empid IS NULL)
          and 
          (date = @date OR @date IS NULL)

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

As long as you have only two parameters, I would say conditional execution(with the help of if...else condition) is best considering the performance aspect.

If you have more parameters, either you can go in two ways:

1. Construct a dynamic query and execute.

2. using the ISNULL condition efficintly(olaf's usggestion or a slightly different approach as below).

Alter  PROCEDURE Proc1(

@empid varchar(50) = NULL,

@date DATETIME   = NULL          
)
AS
BEGIN
SELECT * FROM tbl_emp

WHERE

suserid =ISNULL(@empid,suserid)

and join_date = ISNULL(@date,join_date)

END

exec Proc1 @empid='a123',@date='2010-07-28 00:00:00.000'

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.