Amar Amar - 24 days ago 7
SQL Question

Use a Stored Procedure Parameter in Join Condition only when it is not empty

I wrote a Stored Procedure where i need to Filter records based on passed Parameter.

Create Procedure spGetName_User_Information
@USER VARCHAR(MAX)
AS
BEGIN
-- SET NoCount ON added to prevent extra result sets from interfering With Select statements.
SET NOCOUNT ON

Select val.userName, val.validFrom, val.validUpto, val.totalPoints, per.senderID
from userValidity as val
Inner Join Persons as per

--When @USER in Empty then use this join condition
on val.username = per.userName

--When @USER in Not Empty then use this join condition
on val.username = per.userName AND per.userName in (@User)

SET NOCOUNT OFF
END


My Requirement is filter records in join Condition based on passed parameter i.e., if Parameter is empty join condition should not filter based on parameter but if parameter is not empty then i should use that parameter to filter records in join condition.

NOTE: I have to use Passed parameter in join condition only, i cannot use it in WHERE, HAVING condition in the Select. It should be used only in JOIN "ON" condition.

Answer

Try this:

Create Procedure spGetName_User_Information 
    @USER VARCHAR(MAX)
AS
BEGIN
    -- SET NoCount ON added to prevent extra result sets from interfering     With Select statements.
    SET NOCOUNT ON

    Select val.userName, val.validFrom, val.validUpto, val.totalPoints, per.senderID
    from userValidity as val
    Inner Join Persons  as per

    on ((ISNULL(@User,'') = '') AND (val.username = per.userName)) OR
       ((ISNULL(@User,'') <> '') AND (val.username = per.userName) AND (per.userName in (@User))

    SET NOCOUNT OFF
END