CODEWITHSUNDEEP
sql-serverstored-proceduressql-order-by
CSharpDev
CSharpDev

Reputation: 869

Optional Order By in stored procedure?

I have a somewhat complex stored procedure. I want to enable users to select columns to order by. They should be able to select as many or few columns as wanted.

Is there some way to implement this in the stored procedure? How can I pass column names into the procedure and then reflect those int he order by clause? Noting there will be a variable number of columns.

I understand how to pass parameters, just don't know if this will be possible to dynamically build the order by clause within a stored procedure

Upvotes: 1

Views: 3818

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

Building the ORDER BY dynamically is pretty straightforward. I assume you are passing parameters like:

@OrderByCol1 NVARCHAR(255),
@OrderByCol2 NVARCHAR(255),
...etc...

These may or may not include direction too, e.g. N'MyColumn DESC'. So then you can build this together as follows:

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = N'SELECT ... 
  FROM ...  
  WHERE ... 
  ORDER BY NULL'
  + COALESCE(',' + @OrderByCol1, '')
  + COALESCE(',' + @OrderByCol2, '')
  ...etc...;

PRINT @sql;
--EXEC sp_executesql @sql;

Since we apparently need to recap the entire SQL injection conversation every time an answer even mentions dynamic SQL, I will add some examples.

If they can only ever sort ascending, then you can prevent SQL injection by simply wrapping the parameter values in QUOTENAME(). 

  + COALESCE(',' + QUOTENAME(@OrderByCol1), '')
  + COALESCE(',' + QUOTENAME(@OrderByCol2), '')

Otherwise, you could also split the parameters apart by space (assuming your column names don't contain spaces, which they shouldn't!), and validate that the left side is always present in sys.columns.

IF @OrderByCol1 IS NOT NULL AND EXISTS
(
   SELECT 1 FROM sys.columns 
     WHERE [object_id] = OBJECT_ID('dbo.MyTable')
     AND name = LTRIM(LEFT(@OrderByCol1, CHARINDEX(' ', @OrderByCol1)))
)
BEGIN
  SET @sql += ',' + @OrderByCol1;
END

You may also want to have checks there in case they pass nothing into any of the parameters, or only pass a value into parameter #4, etc. The above does that.

It might be better to pass these in using a TVP, then you don't have to place arbitrary and artificial limits on the number of columns they can choose. Here is one example of a three-column TVP which allows you to pass in a set of order by columns, dictate the order they're applied, and indicate the sort order for each. This also makes it slightly easier to check that each column is really a column (and hey, if you name a column [1;truncate table dbo.something], you deserve what you get...).

First, create the following user-defined table type in your database:

CREATE TYPE dbo.OrderByColumns AS TABLE
(
  [Sequence] TINYINT PRIMARY KEY,
  ColumnName SYSNAME NOT NULL,
  Direction VARCHAR(4) NOT NULL DEFAULT 'ASC'
);

Then:

DECLARE @x dbo.OrderByColumns;

INSERT @x SELECT 1, N'name', 'ASC';
INSERT @x SELECT 2, N'ID', 'DESC';
INSERT @x SELECT 3, N'1;truncate table dbo.whatever', 'DESC';

-- the above could be a parameter to your stored procedure
-- and could be populated in a DataTable in your application

DECLARE @sql NVARCHAR(MAX) = N'SELECT ... FROM ...
  WHERE ... ORDER BY NULL';

SELECT @sql += ',' + QUOTENAME(x.ColumnName) + ' ' + x.Direction
FROM sys.columns AS c
INNER JOIN @x AS x
ON c.name = x.ColumnName
AND c.[object_id] = OBJECT_ID('dbo.MyTable')
ORDER BY x.[Sequence] OPTION (MAXDOP 1);

PRINT @sql;

While you can do this using CASE, generating the ORDER BY dynamically - particularly when it influences the plan choice - can actually be better for performance. With a static query, you get a plan for whatever @order_column was first, then it gets reused even if a different ordering column might have led to a different, more efficient plan. Different plans are likely with different ORDER BY clauses because these require different SORT operators. You can get around this problem somewhat using OPTION (RECOMPILE), which ensures you get a new plan generated every time, but now you pay for a compile cost every single time, even if the same order by is always, or almost always, used.

When you use dynamic SQL, each version of the query is optimized separately. Plan cache bloat is a concern that is somewhat offset by the optimize for ad hoc workloads server setting. This prevents SQL Server from caching the whole plan for a specific variation of a query until that specific variation has been used twice.

Upvotes: 4

Jacob Mattison
Jacob Mattison

Reputation: 51072

You can assemble the SQL dynamically and execute it using sp_executesql. However, that loses you some of the performance and security gains that you get from using a parameterized stored procedure that isn't dynamic.

If the possible ORDER BY columns are a finite list, you can use a CASE WHEN in the ORDER BY clause to alter the ordering based on a passed-in parameter. E.g. if you've passed in a parameter called @order_column, you can do

ORDER BY
CASE WHEN @order_column='ColumnA'
THEN ColumnA END
CASE WHEN @order_column='ColumnB'
THEN ColumnB END

Upvotes: 5

Related Questions