Logic for ‘ALL’ Parameter.

[rpal]

Hi,
I am having problem to write parameter in following procedure.

CREATE PROCEDURE Emp_proc
@EmpNoMin varchar(6)
,@EmpNoMax varchar(6)

AS
BEGIN
SET NOCOUNT ON
SELECT
E.EMPNAME
E.DEPARTMENT
FROM EMP_TBL
WHERE
EMPNO between @EmpNoMin and @EmpNoMax
END

The above procedure gives me to enter only range of employee number at a time.

But some times users want to run procedure for all employees by giving keywork 'ALL' OR
they want to enter in Range of employee number

I want to do some thing like this
WHERE
(@EmpNo='ALL') OR
(EMPNO=@EmpNo)

Can you please help me how to incorporate these logic in the proceudre.

Thx
Raj.

 

[SQLSister]

There is no keyword All that you can search on. The coalesce function might help you out. Just set it tothe lowest possible value for the min and the highest possible value for the max inthe between function if null is sent for the varaiable.

 

[SonOfEmidec1100]

You are pretty close, the key word is OR use it in the where clause.

   SELECT 
    E.EMPNAME
    E.DEPARTMENT
    FROM  EMP_TBL
    WHERE 
        (EMPNO between @EmpNoMin and @EmpNoMax)
        OR (@EmpNo='ALL')

 

[donutman]

SonOfEmidec1100 has an effective idea, but just tell the users to enter 0, 0 for the parameters to get all records and change the WHERE clause to:
WHERE (EMPNO between @EmpNoMin and @EmpNoMax) OR (@EmpNoMin=0 and @EmpNoMax=0)

This assumes that 0 is not a useable EMPNO. If it is then you have to use something else maybe -1 for @EmpNoMax by itself?
-Karl