Using dynamic SQL to handle multiple optional parameters

Previously I mentioned a way to handle stored procedures with multiple optional parameters, that of the recompile hint. That's fine in many cases but since it prevents plan caching for that query, it can result in higher CPU usage, This may can be unacceptable if the procedure is called very frequently or if the server is already under CPU load.

For those cases, converting the procedure to use dynamic SQL may be a better solution.

Let's start with the same example as last time

1

SELECT ProductID, 

2

    ReferenceOrderID, 

3

    TransactionType, 

4

    Quantity,

5

    TransactionDate, 

6

    ActualCost 

7

FROM Production.TransactionHistory

8

WHERE (@ProductID IS NULL OR ProductID = @ProductID)

9

    AND (@OrderStatus IS NULL OR OrderStatus = @OrderStatus)

10

    AND (@OrderTotal IS NULL OR OrderTotal >= @OrderTotal)

11

    AND (@RegionID IS NULL OR Region = @RegionID);

When using Dynamic SQL, there is one very important requirement. User input must NOT be concatenated into a string that will be executed. Doing so causes SQL injection vulnerabilities. SQL Injection has been the cause of a number of high-profile data breaches recently.

Fortunately, concatenating input into the string isn't necessary (it's the easy way, hence why so much of it is seen). Dynamic SQL can be parameterised.

Firstly, the variables and the initial portion of the select statement

1

DECLARE @SQL NVARCHAR(4000), @Where NVARCHAR(4000) = ''

2

SET @SQL = 'SELECT ProductID, ReferenceOrderID, TransactionType, Quantity, TransactionDate, ActualCost

3

from Production.TransactionHistory '

The variables do need to be nvarchar.

For each of the parameters which may or may not be passed, an IF block checks whether or not to add that predicate to the where clause

1

IF (@Product IS NOT NULL)

2

    SET @Where = @Where + 'AND ProductID = @_Product '

3

IF (@OrderID IS NOT NULL)

4

    SET @Where = @Where + 'AND ReferenceOrderID = @_OrderID '

5

IF (@TransactionType IS NOT NULL)

6

    SET @Where = @Where + 'AND TransactionType = @_TransactionType '

7

IF (@Qty IS NOT NULL)

8

    SET @Where = @Where + 'AND Quantity = @_Qty '

Note the use of new parameters inside the dynamic SQL. The values which are being checked are not being added to the string.

You can start the where clause off with WHERE 1=1 AND ..., but I personally dislike that pattern, I rather do this

1

IF (LEN(@Where) > 0)

2

    SET @SQL = @SQL + 'WHERE ' + RIGHT(@Where, LEN(@Where)-3)

Now we have a dynamic SQL string which just contains the predicates that are actually going to filter the data. All that's left is to execute it. This is done with the sp_executeSQL system procedure, allowing for parameters to be passed to the dynamic string.

The first parameter to sp_executeSQL is the string to be executed. The second is the parameter list, the third onwards assigns values to the parameters

1

EXEC sp_executesql @SQL,

2

    N'@_Product int, @_OrderID int, @_TransactionType char(1), @_Qty int',

3

    @_Product = @Product, @_OrderID = @OrderID, @_TransactionType = @TransactionType, @_Qty = @Qty

from : https://yoda.entelect.co.za/view/3279/using-dynamic-sql-to-handle-multiple-optional-parameters