SQL WHERE IN from a parameter or variable

Question

Context

We are currently cleaning up the SQL database, and we are faced with a large number of stored procedures that have only slight differences between them. We want to combine them into a single process in order to simplify maintenance.

Problem

Below are just two examples of the type of stored processes that we are trying to combine (note that these are simplified versions, not actual procs).

Stored Procedure - Current Orders

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 

Saved Procedure - Archived Orders

 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 that calls 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 procedures is the values ​​provided by WHERE IN.

Is there a way to change this and have a list of values ​​provided through a parameter or variable?

Answer 1

If the goal is to reduce maintenance efforts, I would humbly suggest that moving data from the data layer and hard-coding it in a logical layer, possibly in several places, may not be conducive to that goal.

Removing these explicit values ​​from the queries will remove the information that the optimizer can use to create the plan. Be careful, you will replace it with something better that may affect query performance. I would suggest that these SPs are separated exactly so that there are unique plans for each, especially if the queries are much more complicated than shown. Compare current plans with each other and everything that you can do so that you do not refuse.

One option might be to create a new List table:

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

Join this table instead of using the IN clause. Qualify the connection using ListName, which is passed as a parameter. A unique clustered index in (ListName, StatusCode) would be nice. You might consider creating filtered statistics for each ListName. Create a foreign key constraint if you keep the main 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 

Caller gets 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) 

Thus, the values ​​for status codes are recorded in one place, and good statistics are available for the optimizer. Whether this is faster than the current implementation, only testing can tell.

Answer 2

"We want to combine them into one process to simplify service."

I would agree, having several stored procedures that do the same thing except for a certain condition can be hellish service; especially if they are everywhere. Below I have several different options and / or solutions that you could try. With that in mind, I’ll let you decide how you would like to approach this, since each option is different from execution plans, etc.

"the only difference between the above stored procedures is the values ​​provided by WHERE IN"

Option 1

• Create a system table, for example: dbo.System_Booking_Status . Only a few columns are required for this table: System_Status_ID (primary key) , Booking_Status_Code INT NOT NULL, and Booking_Status BIT NOT NULL

Now you can fill this table with your values. The column of the reservation status can mean 1 for the current and 2 for the archive; no matter how you wish. Or enter the VarChar(15) type VarChar(15) and use the description (current or archived) so that it differs or the type Char(1) ( C ) is used for the current and A for the archive).

Now that you have a system table with which you can join , we need to tell the stored procedure this and when. I used a new parameter: the status that should be passed when you make a call to either receive current or archived records.

 ALTER PROCEDURE [dbo].[SelectCurrentBookings] @client_FK INT, @startDate DATETIME, @endDate DATETIME, @status INT AS BEGIN IF @status = 1 --current BEGIN SELECT ROW_NUMBER() OVER (ORDER BY b.[bookingDateTime]) [RowNumber], b.[booking_PK] [Booking ID], b.[bookingDateTime] [Booking Date], b.[bookingDuration] [Duration], b.[client] [Client Name], CASE WHEN b.[bookingStatusCode_FK] IN (4,8,9,7,16) THEN 1 ELSE 0 END [Unserviced], FROM [Booking] b INNER JOIN dbo.System_Booking_Status sbs ON sbs.Booking_Status_Code = b.bookingStatusCode_FK AND sbs.Booking_Status = 1 --current? WHERE [client_FK] = @client_FK AND b.[bookingDateTime] >= @startDate AND b.[bookingDateTime] < DATEADD(d,1,@endDate) AND b.[deleted] = 0 END ELSE BEGIN SELECT ROW_NUMBER() OVER (ORDER BY b.[bookingDateTime]) [RowNumber], b.[booking_PK] [Booking ID], b.[bookingDateTime] [Booking Date], b.[bookingDuration] [Duration], b.[client] [Client Name], CASE WHEN b.[bookingStatusCode_FK] IN (4,8,9,7,16) THEN 1 ELSE 0 END [Unserviced], FROM [Booking] b INNER JOIN dbo.System_Booking_Status sbs ON sbs.Booking_Status_Code = b.bookingStatusCode_FK AND sbs.Booking_Status = 2 --archived? WHERE [client_FK] = @client_FK AND b.[bookingDateTime] >= @startDate AND b.[bookingDateTime] < DATEADD(d,1,@endDate) AND b.[deleted] = 0 END END 

Now you do not need to use hard-coded values ​​and exclude IN clause with INNER JOIN .

Option Two (fast n dirty)

• Pass another variable to determine if you want to return current or archived records.

  IF @status = 1 .... START - A normal request is made for active ... END ELSE START - A normal request is made for archiving ... END

Option three

You can pass XML values ​​that you need. Divide this XML into Table Variable and attach to it. It will also eliminate your IN clause and a better execution plan.

For instance:

  Declare @Status_Codes XML = '' DECLARE @StatusTable TABLE ( Status_Code INT ) -- Parse the XML in to the temp table declared above INSERT INTO @StatusTable(Status_Code) SELECT xmlData.A.value('.', 'INT') FROM @Status_Codes.nodes('BookingStatus/StatusCode/Code') xmlData(A) 

Now you have a Table Variable with which you can join ...

  FROM [Booking] b INNER JOIN @StatusTable s ON s.Status_Code = b.bookingStatusCode_FK 

Then you must pass this data from your function call. You can create a function that returns formatted XML and pass this to your stored procedure call.

  Public Shared Function ConstructXMLStatusCodes(ByVal intStatus As Integer) As String Dim strBuilder As New System.Text.StringBuilder If intStatus = 1 'active... With strBuilder .AppendLine("<BookingStatus>") .AppendLine("<StatusCode>") .AppendLine("<Code>1</Code>") .AppendLine("<Code>2</Code>") 'continue to add more codes... .AppendLine("</StatusCode>") .AppendLine("</BookingStatus>") End With Else 'Create your builder with the archive... End If Return strBuilder.ToString() End Function 

Then make your call before passing it to your function or make a call in your function; you have several options ...

In another note

I notice that you are executing a DataSet , but you never return it, I would use the DataTable.Load method, which reads and populates the table ...

Good luck