SPROC shuold return all rows if optional parameter is null

[manjulam]

In the following sproc, if @SS2BRUserID is not passed, I want it to return all the rows (equivalent to commenting out 'AND @SS2BRUserID IS NULL OR C.SS2BRUserID=@SS2BRUserID'). It doesnt seem to be working for me. Can anyone help me with this?


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[SP_REPORTSUMMARY]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[SP_REPORTSUMMARY]
GO


CREATE PROCEDURE [dbo].[SP_REPORTSUMMARY]
@SS2BRUserID int=NULL
AS

BEGIN
SELECT C.CampaignID,C.CampaignName,C.Status,C.AvailabletoGeneralPublic,C.Cost,
COUNT(
CASE
WHEN CC.CampaignStatus = 1
THEN CC.CampaignStatus
ELSE NULL
END
)
AS ActiveUsers,
COUNT(
CASE
WHEN CC.CampaignStatus = 3
THEN CC.CampaignStatus
ELSE NULL
END
)
AS CompletedUsers,
COUNT(
CASE
WHEN CC.CampaignStatus = 4
THEN CC.CampaignStatus
ELSE NULL
END
)
AS DroppedOutUsers,
COUNT(
CASE
WHEN CC.CampaignStatus NOT IN(1,3,4)
THEN CC.CampaignStatus
ELSE NULL
END
)
AS AllOtherUsers,
U.FirstName + ' ' + U.LastName AS ProgramOwner
FROM CampaignConsumer CC
INNER JOIN Campaign C
ON CC.CampaignID=C.CampaignID
INNER JOIN SS2BRUser U
ON C.SS2BRUserID=U.SS2BRUserID
AND @SS2BRUserID IS NULL OR C.SS2BRUserID=@SS2BRUserID
GROUP BY U.FirstName,U.LastName,
C.CampaignID,C.CampaignName,C.Status,C.AvailabletoGeneralPublic,C.Cost
ORDER BY U.FirstName,C.CampaignID
END

 

[RyanEK]

Hi,

You may need to encase the conditions in brackets

...
 INNER JOIN SS2BRUser U
    ON C.SS2BRUserID=U.SS2BRUserID
    AND (@SS2BRUserID IS NULL OR C.SS2BRUserID=@SS2BRUserID)
    GROUP BY U.FirstName,U.LastName,
...

Ryan

 

[manjulam]

I did try that. It returns nothing when the parameter is null. I guess it has something to do with the grouping and joins. The condition has to be C.SS2BRUserID IS NOT NULL for it to return all rows. I changed it to have two SELECT clauses conditionally and made it work for now. If you have a better idea, let me know.

 

[bborissov]

Put it in WHERE Clause not in JOIN condition:

 INNER JOIN SS2BRUser U ON C.SS2BRUserID=U.SS2BRUserID

....
WHERE (@SS2BRUserID IS NULL OR C.SS2BRUserID=@SS2BRUserID)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.