CODEWITHSUNDEEP
sql-servert-sql
Zub Hoss
Zub Hoss

Reputation: 9

Create dynamic where clause in Stored Procedure

I have created the following procedure:

ALTER PROCEDURE [dbo].[sp_getQuickReportsData] 
    @dateClosedFrom DATE,
    @dateClosedTo DATE,
    @dateNotifiedFrom DATE,
    @dateNotifiedTo DATE,
    @filter NVARCHAR(50) = NULL,
    @filterValues NVARCHAR(500) = NULL
AS
BEGIN

    SET NOCOUNT ON;

    SELECT
        b.FirstName + ' ' + b.LastName AS ClaimHandler
    , c.[Claim Ref] AS ClaimRef
    , c.Profession
    , c.Insured
    , c.ClientDivision
    , c.ClientSiteOrAccidentLossLocation AS LossLocation
    FROM 
    ClaimData c LEFT JOIN CSL_LIB.dbo.Security_Users b ON b.ID = c.ClaimHandlerID
    WHERE 
        DateClosed >= @dateClosedFrom AND DateClosed <= @dateClosedTo
    AND 
        DateNotified >= @dateNotifiedFrom AND DateNotified <= @dateNotifiedTo
    
END
GO

I now have added two new non required parameters called @filter and @filtervalues. Basically I sometimes can pass values in for the @filter parameter and for the @filterValues parameter.

I want to add an extra WHERE clause to the stored procedure if @filter and @filtervalues has data passed in. For example:

@filter = 'claimhandler'
@filtervalues = 'bob,tom,pete'

I would like to extend the WHERE clause in my stored procedure based on the following logic:

IF @filter = 'claimhandler'
 THEN AND claimhandler in @filterValues
END

IF @filter = 'reference'
 THEN AND reference in @filterValues
END

I'm not sure how to add this dynamic SQL logic into my stored procedure. Any help would be appreciated.

Have tried something like the following:

ALTER PROCEDURE [dbo].[sp_getQuickReportsData] 
    @dateClosedFrom DATE,
    @dateClosedTo DATE,
    @dateNotifiedFrom DATE,
    @dateNotifiedTo DATE,
    @filter NVARCHAR(50) = NULL,
    @filterValues NVARCHAR(500) = NULL
AS
BEGIN

    SET NOCOUNT ON;
    SELECT
b.FirstName + ' ' + b.LastName AS ClaimHandler
    , c.[Claim Ref] AS ClaimRef
    , c.Profession
    , c.Insured
    , c.ClientDivision
    , c.ClientSiteOrAccidentLossLocation AS LossLocation
FROM 
    ClaimData c LEFT JOIN CSL_LIB.dbo.Security_Users b ON b.ID = c.ClaimHandlerID
    WHERE 
        DateClosed >= @dateClosedFrom AND DateClosed <= @dateClosedTo
    AND 
        DateNotified >= @dateNotifiedFrom AND DateNotified <= @dateNotifiedTo

    case when @filter = 'claimhandler' THEN
        AND claimghandler in @filterValues

    case when @filter = 'reference' THEN
          AND reference in @filterValues

    END

END
GO

Upvotes: 0

Views: 75

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76943

You can easily achieve that with case-when:

        @filterValues LIKE
        CASE
            WHEN @filter = 'claimhandler' THEN CONCAT('%,', claimhandler, ',%')
            WHEN @filter = 'reference' THEN CONCAT('%,', reference, ',%')
            ELSE @filterValues
        END

and add it to your WHERE clause:

    WHERE 
        DateClosed >= @dateClosedFrom AND DateClosed <= @dateClosedTo
    AND 
        DateNotified >= @dateNotifiedFrom AND DateNotified <= @dateNotifiedTo
    AND
        @filterValues LIKE
        CASE
            WHEN @filter = 'claimhandler' THEN CONCAT('%,', claimhandler, ',%')
            WHEN @filter = 'reference' THEN CONCAT('%,', reference, ',%')
            ELSE @filterValues
        END

if you want to support multiple filters, then you can do this:

        @filterValues LIKE
        CASE
            WHEN @filter = 'claimhandler' THEN CONCAT('%,', claimhandler, ',%')
            ELSE @filterValues
        END
        AND
        @filterValues LIKE
        CASE
            WHEN @filter = 'reference' THEN CONCAT('%,', reference, ',%')
            ELSE @filterValues
        END

to test them separately. Now this is the format for filterValues that I propose:

',ABC,3,DEF,21B,'

note that it's a string with the elements delimited by , and we prepend and append , too, so we can seek the filter values to be LIKE '%,yourvalue,%', but since it's dynamic, it's concatenated.

Fiddle.

Upvotes: -1

Related Questions