To practice what you’ve learned so far, try to provide a solution to the following task: write a stored procedure called usp_GetSortedShippers that accepts a column name from the Shippers table in the Northwind database as one of the inputs (@colname), and that returns the rows from the table sorted by the input column name. Assume also that you have a sort direction as input (@sortdir), with the value ′A′ representing ascending order and ′D′ representing descending order. The stored procedure should be written with performance in mind–that is, it should use indexes when appropriate (for example, a clustered or nonclustered covering index on the sort column).

Example 7-4 shows the first suggested solution for the task.

USE Northwind;
GO
IF OBJECT_ID('dbo.usp_GetSortedShippers') IS NOT NULL
  DROP PROC dbo.usp_GetSortedShippers;
GO
CREATE PROC dbo.usp_GetSortedShippers
  @colname AS sysname, @sortdir AS CHAR(1) = 'A'
AS

IF @sortdir = 'A'
  SELECT ShipperID, CompanyName, Phone
  FROM dbo.Shippers
  ORDER BY
    CASE @colname
      WHEN N'ShipperID'   THEN CAST(ShipperID AS SQL_VARIANT)
      WHEN N'CompanyName' THEN CAST(CompanyName AS SQL_VARIANT)
      WHEN N'Phone'       THEN CAST(Phone AS SQL_VARIANT)
    END
ELSE
  SELECT ShipperID, CompanyName, Phone
  FROM dbo.Shippers
  ORDER BY
    CASE @colname
      WHEN N'ShipperID'   THEN CAST(ShipperID AS SQL_VARIANT)
      WHEN N'CompanyName' THEN CAST(CompanyName AS SQL_VARIANT)
      WHEN N'Phone'       THEN CAST(Phone AS SQL_VARIANT)
    END DESC;
GO

The solution uses an IF statement to determine which of two queries to run based on the requested sort direction. The only difference between the queries is that one uses an ascending order for the sort expression and the other a descending one. Each query uses a single CASE expression that returns the appropriate column value based on the input column name.

Note

SQL Server determines the datatype of the result of a CASE expression based on the datatype with the highest precedence among the possible result values of the expression; not by the datatype of the actual returned value. This means, for example, that if the CASE expression returns a VARCHAR(30) value in one of the THEN clauses and an INT value in another, the result of the expression will always be INT, because INT is higher in precedence than VARCHAR. If in practice the VARCHAR(30) value is returned, SQL Server will attempt to convert it. If the value is not convertible, you get a runtime error. If it is convertible, it becomes an INT and, of course, might have a different sort behavior than the original value.

To avoid such issues, I simply converted all the possible return values to SQL_VARIANT. SQL Server will set the datatype of the CASE expression to SQL_VARIANT, but it will preserve the original base types within that SQL_VARIANT.

Run the following code to test the solution, requesting to sort the shippers by ShipperID in descending order, and it will generate the output shown in Table 7-11:

EXEC dbo.usp_GetSortedShippers N'ShipperID', N'D';

The output is logically correct, but notice the plan generated for the stored procedure, shown in Figure 7-4.

Figure 7-4. Execution plan showing a table scan (unordered clustered index scan) and a sort operator

Remember that the optimizer cannot rely on the sort that the index maintains if you performed manipulation on the sort column. The plan shows a table scan (unordered clustered index scan) followed by an explicit sort operation. For the problem the query was intended to solve, an optimal plan would have performed an ordered scan operation in the clustered index defined on the ShipperID column–eliminating the need for an explicit sort operation.

Example 7-5 shows the second solution for the task.

ALTER PROC dbo.usp_GetSortedShippers
  @colname AS sysname, @sortdir AS CHAR(1) = 'A'
AS

SELECT ShipperID, CompanyName, Phone
FROM dbo.Shippers
ORDER BY
  CASE WHEN @colname = N'ShipperID'   AND @sortdir = 'A'
    THEN ShipperID   END,
  CASE WHEN @colname = N'CompanyName' AND @sortdir = 'A'
    THEN CompanyName END,
  CASE WHEN @colname = N'Phone'       AND @sortdir = 'A'
    THEN Phone       END,
  CASE WHEN @colname = N'ShipperID'   AND @sortdir = 'D'
    THEN ShipperID   END DESC,
  CASE WHEN @colname = N'CompanyName' AND @sortdir = 'D'
    THEN CompanyName END DESC,
  CASE WHEN @colname = N'Phone'       AND @sortdir = 'D'
    THEN Phone       END DESC;
GO

This solution uses CASE expressions in a more sophisticated way. Each column and sort direction combination is treated with its own CASE expression. Only one of the CASE expressions will yield TRUE for all rows, given the column name and sort direction that particular CASE expression is looking for. All other CASE expressions will return NULL for all rows. This means that only one of the CASE expressions–the one that looks for the given column name and sort direction–will affect the order of the output.

Run the following code to test the stored procedure:

EXEC dbo.usp_GetSortedShippers N'ShipperID', N'D';

Though this stored procedure applies an interesting logical manipulation, it doesn’t change the fact that you perform manipulation on the column and don’t sort by it as is. This means that you will get a similar nonoptimal plan to the one shown earlier in Figure 7-4.

Example 7-6 shows the third solution for the task.

ALTER PROC dbo.usp_GetSortedShippers
  @colname AS sysname, @sortdir AS CHAR(1) = 'A'
AS

IF @colname NOT IN (N'ShipperID', N'CompanyName', N'Phone')
BEGIN
  RAISERROR('Possible SQL injection attempt.', 16, 1);
  RETURN;
END

DECLARE @sql AS NVARCHAR(4000);

SET @sql = N'SELECT ShipperID, CompanyName, Phone
FROM dbo.Shippers
ORDER BY '
  + QUOTENAME(@colname)
  + CASE @sortdir WHEN 'D' THEN N' DESC' ELSE '' END
  + ';';

EXEC sp_executesql @sql;
GO

This solution simply uses dynamic execution, concatenating the input column name and sort direction to the ORDER BY clause of the query. In terms of performance the solution achieves our goal–namely, it will use an index efficiently if an appropriate one exists. To see that it does, run the following code:

EXEC dbo.usp_GetSortedShippers N'ShipperID', N'D';

Observe in the execution plan shown in Figure 7-5 that the plan performs an ordered backward clustered index scan with no sort operator, which is optimal for these inputs.

Figure 7-5. Execution plan showing ordered backward clustered index scan

Another advantage of this solution is that it’s easy to maintain. The downside of this solution is the use of dynamic execution, which involves many security-related issues (for example, ownership chaining and SQL injection if the inputs are not validated). For details about security issues related to dynamic execution, please refer to Chapter 4.

The fourth solution that I’ll cover is shown in Example 7-7.

CREATE PROC dbo.usp_GetSortedShippers_ShipperID_A
AS
  SELECT ShipperID, CompanyName, Phone
  FROM dbo.Shippers
  ORDER BY ShipperID;
GO
CREATE PROC dbo.usp_GetSortedShippers_CompanyName_A
AS
  SELECT ShipperID, CompanyName, Phone
  FROM dbo.Shippers
  ORDER BY CompanyName;
GO
CREATE PROC dbo.usp_GetSortedShippers_Phone_A
AS
  SELECT ShipperID, CompanyName, Phone
  FROM dbo.Shippers
  ORDER BY Phone;
GO
CREATE PROC dbo.usp_GetSortedShippers_ShipperID_D
AS
  SELECT ShipperID, CompanyName, Phone
  FROM dbo.Shippers
  ORDER BY ShipperID   DESC;
GO
CREATE PROC dbo.usp_GetSortedShippers_CompanyName_D
AS
  SELECT ShipperID, CompanyName, Phone
  FROM dbo.Shippers
  ORDER BY CompanyName DESC;
GO
CREATE PROC dbo.usp_GetSortedShippers_Phone_D
AS
  SELECT ShipperID, CompanyName, Phone
  FROM dbo.Shippers
  ORDER BY Phone       DESC;
GO

ALTER PROC dbo.usp_GetSortedShippers
  @colname AS sysname, @sortdir AS CHAR(1) = 'A'
AS

IF @colname = N'ShipperID'        AND @sortdir = 'A'
  EXEC dbo.usp_GetSortedShippers_ShipperID_A;
ELSE IF @colname = N'CompanyName' AND @sortdir = 'A'
  EXEC dbo.usp_GetSortedShippers_CompanyName_A;
ELSE IF @colname = N'Phone'       AND @sortdir = 'A'
  EXEC dbo.usp_GetSortedShippers_Phone_A;
ELSE IF @colname = N'ShipperID'   AND @sortdir = 'D'
  EXEC dbo.usp_GetSortedShippers_ShipperID_D;
ELSE IF @colname = N'CompanyName' AND @sortdir = 'D'
  EXEC dbo.usp_GetSortedShippers_CompanyName_D;
ELSE IF @colname = N'Phone'       AND @sortdir = 'D'
  EXEC dbo.usp_GetSortedShippers_Phone_D;
GO

This solution might seem childish at first glance. You create a separate stored procedure with a single static query for each possible combination of inputs. Then, usp_GetSortedShippers can act as a redirector. Simply use a series of IF / ELSE IF statements to check for each possible combination of inputs, and you explicitly invoke the appropriate stored procedure for each. Sure, it is a bit long and requires more maintenance than the previous solution, but it uses static queries that generate optimal plans. Note that each query will get its own plan and will be able to reuse a previously cached plan for the same query.

To test the procedure, run the following code:

EXEC dbo.usp_GetSortedShippers N'ShipperID', N'D';

You will get the optimal plan for the given inputs, similar to the plan shown earlier in Figure 7-5.

When you’re done, run the following code for cleanup:

IF OBJECT_ID('dbo.usp_GetSortedShippers') IS NOT NULL
  DROP PROC dbo.usp_GetSortedShippers;
IF OBJECT_ID('dbo.usp_GetSortedShippers_ShipperID_A') IS NOT NULL
  DROP PROC dbo.usp_GetSortedShippers_ShipperID_A;
IF OBJECT_ID('dbo.usp_GetSortedShippers_CompanyName_A') IS NOT NULL
  DROP PROC dbo.usp_GetSortedShippers_CompanyName_A;
IF OBJECT_ID('dbo.usp_GetSortedShippers_Phone_A') IS NOT NULL
  DROP PROC dbo.usp_GetSortedShippers_Phone_A;
IF OBJECT_ID('dbo.usp_GetSortedShippers_ShipperID_D') IS NOT NULL
  DROP PROC dbo.usp_GetSortedShippers_ShipperID_D;
IF OBJECT_ID('dbo.usp_GetSortedShippers_CompanyName_D') IS NOT NULL
  DROP PROC dbo.usp_GetSortedShippers_CompanyName_D;
IF OBJECT_ID('dbo.usp_GetSortedShippers_Phone_D') IS NOT NULL
  DROP PROC dbo.usp_GetSortedShippers_Phone_D;