VB-Stored Procedure...for Crystal.

[vbjohn]

I have a stored procedure in Sql and I have the Stored Procedure in a conditional format.

What my problem is that when I run this and I put a @Benefit = 'MATCH' I get data. But when I select @Benefit = NULL (which I am supposed to get everything, nothing comes up. What am I doing wrong? Here is my code in my SP:


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

 

[sunila7]

Hi,
Change ur SP like this...

CREATE PROCEDURE dbo.BenefitEditRep @SEmpNum char(6) = Null, @EEmpNum char(6) = Null, @Benefit char(6) = Null AS
IF @Benefit is not NULL
BEGIN
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
END
ELSE
BEGIN
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
END
GO