Stored Procedure...Conditional????

[vbjohn]

I was wondering if there is a way that I could add in an IF STATEMENT in a Stored Procedure? If so how would I do it? This is what I have and I know that it is wrong... any suggestions would be great.



CREATE PROCEDURE dbo.BenefitEditRep @SEmpNum char(6) = Null, @EEmpNum char(6) = Null, @Benefit char(6) = Null AS

SELECT * FROM
UPR00100 INNER JOIN UPR00600 ON
UPR00100.EMPLOYID = UPR00600.EMPLOYID
IF @Benefit <> NULL THEN
WHERE
UPR00600.BENEFIT = @Benefit AND
UPR00100.EMPLOYID >= @SEmpNum AND
UPR00100.EMPLOYID <= @EEmpNum AND
UPR00100.INACTIVE = 0
ELSE
WHERE
UPR00100.EMPLOYID >= @SEmpNum AND
UPR00100.EMPLOYID <= @EEmpNum AND
UPR00100.INACTIVE = 0
END IF
ORDER BY
UPR00100.EMPLOYID ASC
GO



John-

 

[sunila7]

Hi,

Is this wht u r looking for

CREATE PROCEDURE dbo.BenefitEditRep @SEmpNum char(6) = Null, @EEmpNum char(6) = Null, @Benefit char(6) = Null AS

IF @Benefit <> NULL
SELECT * FROM
UPR00100 INNER JOIN UPR00600 ON
UPR00100.EMPLOYID = UPR00600.EMPLOYID

WHERE
UPR00600.BENEFIT = @Benefit AND
UPR00100.EMPLOYID >= @SEmpNum AND
UPR00100.EMPLOYID <= @EEmpNum AND
UPR00100.INACTIVE = 0
ORDER BY
UPR00100.EMPLOYID ASC
ELSE
SELECT * FROM
UPR00100 INNER JOIN UPR00600 ON
UPR00100.EMPLOYID = UPR00600.EMPLOYID
WHERE
UPR00100.EMPLOYID >= @SEmpNum AND
UPR00100.EMPLOYID <= @EEmpNum AND
UPR00100.INACTIVE = 0
ORDER BY
UPR00100.EMPLOYID ASC
GO


Sunil

 

[vbjohn]

Yes it is! Thanks so very much!

john-

 

[swampBoogie]

You should use

IF @Benefit is not NULL