Multiple branches for query - simplified?

Transact-SQL
1

So imagine where you have a SPROC and you may have up to 6 fields to potentially perform a match on.
What is the best and simplest way of doing this?

For instance, imagine us having the following params:

how would we do an efficient match from a table where any one of these parameters could potentially be sent to the SPROC? I wouldn't want to do something like:

is there another better way?
Sure, could build a dynamic SQL string and execute that but then it makes the SPROC a bit irrelevant when the client just generate and execute it.

Thank you.

2

This is one of those rare cases where dynamic SQL is indeed better. You can create a stored procedure that generates the query string based on the parameters and executes it. So from the clients perspective, it will look no different.

These two articles would be very useful. They explain why this is better, and how to do it without the risk of SQL injection attacks.

  1. Sommarskog
  2. Gail Shaw

The second one is short and simple and gets to the point quickly. The first one is very thorough and in-depth.

3

Thanks. It's funny. I was trying to avoid it due to the fact that someone can argue that there wouldn't be a point creating a SPROC which does dynamic SQL when the end client (i.e a .NET app) can generate the SQL and just execute it.
This then eliminates the need for a SPROC.

So, what about things like paging if I were to use dynamic SQL in this manner ? I usually do the paging using the CTE approach.

4

For a variety of reasons - security, maintainability etc. - I make it a point to give access to data only via stored procedures.

As for paging, you could add the code for wrapping the query results into a CTE and picking the appropriate subset into the dynamic SQL itself. Alternatively, you could use insert-exec to get the results into a temp table and then pick the subset. I prefer the former because of some known limitations with insert-exec.

5

Thought as much :smile:
Sorry - can you kindly whip up a quick example of using the CTE approach for this type of dynamic query involving paging?

6

See the example below where I modified Gail Shaw's stored proc to add a start row and end row number for the results to be returned. Print out the @sSQL string before the sp_execute to see if it parses correctly and is exactly what you want.

CREATE PROCEDURE SearchHistory_Dynamic
    (
      @Product INT = NULL ,
      @OrderID INT = NULL ,
      @TransactionType CHAR(1) = NULL ,
      @Qty INT = NULL,
      @startRow INT,
      @endRow INT
    )
AS 
    DECLARE @sSQL NVARCHAR(2000) ,
        @Where NVARCHAR(1000) = ''
    SET @sSQL = ';WITH CTE AS ('; 
    SET @sSQL = @sSQL + 'SELECT ProductID, ReferenceOrderID, TransactionType, Quantity, TransactionDate, ActualCost,
    ROW_NUMBER() OVER( ORDER BY TransactionDate DESC) as RN 
	from Production.TransactionHistory '
 
    IF @Product IS NOT NULL 
        SET @Where = @Where + 'AND ProductID = @_Product '
    IF @OrderID IS NOT NULL 
        SET @Where = @Where + 'AND ReferenceOrderID = @_OrderID '
    IF @TransactionType IS NOT NULL 
        SET @Where = @Where + 'AND TransactionType = @_TransactionType '
    IF @Qty IS NOT NULL 
        SET @Where = @Where + 'AND Quantity = @_Qty '
 
    IF LEN(@Where) > 0 
        SET @sSQL = @sSQL + 'WHERE ' + RIGHT(@Where, LEN(@Where) - 3)
        
    SET @sSQL = @sSQL + ') SELECT * FROM CTE WHERE RN >= @_startRow AND RN <= @_endRow '
 
    EXEC sp_executesql @sSQL,
        N'@_Product int, @_OrderID int, @_TransactionType char(1), @_Qty int, @_startRow INT, @_endRow INT',
        @_Product = @Product, @_OrderID = @OrderID,
        @_TransactionType = @TransactionType, @_Qty = @Qty,
        @_startRow = @startRow, @_endRow = @endRow;
 
GO