SQL Server: dynamic ORDER BY and if input param is NULL do not ORDER BY?

I am trying to modify a stored procedure that I have to include a Input parameter @OrderBy.

But I need to test it to say if its NULL then don't include the ORDER BY in the SELECT SQL

If its NOT null then 开发者_如何转开发I want to order by on @OrderBy

Is this possible?

Thanks in advance

you can accomplish this using dynamic SQL

Declare @dynamicSQL nvarchar(5000);
Declare @orderBy nvarchar(50);

Set @orderBy = 'column1';

Set @dynamicSQL = 'Select * from Table ';

If (@orderBy IS NOT NULL) BEGIN
  @dynamicSQL = @dynamicSQL + 'Order BY ' + @orderBy;
END

exec @dynamicSQL;

The only way to do it like you say purely is to use dynamic SQL, but a CASE statement often works just as well:

SELECT *
FROM foo f
ORDER BY CASE WHEN @OrderBy IS NULL THEN f.Whatever ELSE '' END