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?

SQL WHERE IN from param or variable

2 Answers