SQL WHERE IN from param or variable

Question

Context

We are currently in the process of cleaning up an SQL database and we've come across a large amount of Stored Procedures which only have slight differences between them. We wish to consolidate them into a single proc to allow for easier maintenance.

Problem

Below are just two examples of the kind of stored procs we're attempting to merge (do note, these are simplified versions, not the actual procs).

Stored Procedure - Current Bookings

ALTER PROCEDURE [dbo].[SelectCurrentBookings]
    @client_FK INT, @startDate DATETIME, @endDate DATETIME
AS
BEGIN
    SELECT  ROW_NUMBER() OVER (ORDER BY [Booking].[bookingDateTime])            [RowNumber],
            [Booking].[booking_PK]      [Booking ID],
            [Booking].[bookingDateTime] [Booking Date],
            [Booking].[bookingDuration] [Duration],
            [Booking].[client]          [Client Name],
            CASE WHEN [Booking].[bookingStatusCode_FK] IN (4,8,9,7,16) THEN 1 ELSE 0 END    [Unserviced],
    FROM    [Booking]
    WHERE   [client_FK] = @client_FK
            AND [Booking].[bookingStatusCode_FK] IN (1,2,14,17)
            AND [Booking].[bookingDateTime] >= @startDate
            AND [Booking].[bookingDateTime] < DATEADD(d,1,@endDate)
            AND [Booking].[deleted] = 0
END

Stored Procedure - Archived Bookings

ALTER PROCEDURE [dbo].[SelectArchivedBookings]
    @client_FK INT, @startDate DATETIME, @endDate DATETIME
AS
BEGIN
    SELECT  ROW_NUMBER() OVER (ORDER BY [Booking].[bookingDateTime])            [RowNumber],
            [Booking].[booking_PK]      [Booking ID],
            [Booking].[bookingDateTime] [Booking Date],
            [Booking].[bookingDuration] [Duration],
            [Booking].[client]          [Client Name],
            CASE WHEN [Booking].[bookingStatusCode_FK] IN (4,8,9,7,16) THEN 1 ELSE 0 END    [Unserviced],
    FROM    [Booking]
    WHERE   [client_FK] = @client_FK
            AND [Booking].[bookingStatusCode_FK] IN (1,2,14,4,9,7,16,13)
            AND [Booking].[bookingDateTime] >= @startDate
            AND [Booking].[bookingDateTime] < DATEADD(d,1,@endDate)
            AND [Booking].[deleted] = 0
END

The code which invokes the stored procs is in VB.NET

Dim Command As DbCommand = _db.GetStoredProcCommand("SelectCurrentBookings")
_db.AddInParameter(Command, "client_FK", DbType.Int32, ClientID)
_db.AddInParameter(Command, "startDate", DbType.DateTime, StartDate)
_db.AddInParameter(Command, "endDate", DbType.DateTime, EndDate)
Return _db.ExecuteDataSet(Command)

As you can see, the only difference between the above stored procs are the values supplied to the WHERE IN.

Is there a way for us to modify this and have the list of values supplied through a parameter or variable?

Answer 1

If the objective is to reduce maintenance effort I would humbly suggest that moving data values out of the data layer and hard-coding them in the logic layer, perhaps in several places, is maybe not contributing to this objective.

Removing these explicit values from the queries will remove information the optimiser can use to create a plan. Be careful you replace it with something better else query performance may suffer. I would posit these SPs are separated precisely so unique plans exist for each, especially if the queries are much more complex than is shown. Compare the current plans against each other and whatever you end up with to ensure you haven't regressed.

One option may be to create a new "list" table:

ListName  StatusCode
current   1
current   2
...
current   17
archive   1
archive   2
archive   4
...
archive   16

Join to this table instead of using an IN clause. Qualify the join by ListName, which is passed as a parameter. A unique clustered index on (ListName, StatusCode) would be good. You may consider creating a filtered statistic for each ListName. Create a foreign key constraint if you hold a master list of status values.

The stored procedure then becomes

ALTER PROCEDURE [dbo].[SelectCurrentBookings]
    @client_FK INT, @startDate DATETIME, @endDate DATETIME, 
    @ListName char(10)
AS
BEGIN
    SELECT  ROW_NUMBER() OVER (ORDER BY [Booking].[bookingDateTime]) [RowNumber],
            ...
    FROM    [Booking]
    INNER JOIN dbo.List as l
        ON [Booking].[bookingStatusCode_FK] = l.StatusCode
        AND l.ListName = @ListName 
    WHERE   [client_FK] = @client_FK
            AND [Booking].[bookingDateTime] >= @startDate
            AND [Booking].[bookingDateTime] < DATEADD(d,1,@endDate)
            AND [Booking].[deleted] = 0
END

The calling code gains a parameter

Dim Command As DbCommand = _db.GetStoredProcCommand("SelectCurrentBookings")
_db.AddInParameter(Command, "client_FK", DbType.Int32, ClientID)
_db.AddInParameter(Command, "startDate", DbType.DateTime, StartDate)
_db.AddInParameter(Command, "endDate", DbType.DateTime, EndDate)
_db.AddInParameter(Command, "ListName", DbType.String, "current") //correct type needed
Return _db.ExecuteDataSet(Command)

This way meanings for status codes are recorded in one place and good statistics are available to the optimiser. Whether this is faster than the current implementation, only testing can tell.