I'm doing some testing with dynamic filtering and order by clause.
I'm comparing dynamic SQL vs using the CASE statement...
DECLARE @FilterName NVARCHAR(100) = 'M%'
DECLARE @FilterSalary FLOAT = 3000
DECLARE @OrderField INT = 0 --0 empname, 1 salary, 2 - empid
DECLARE @Query NVARCHAR(500)
DECLARE @Params NVARCHAR(500)
SET @Query = 'SELECT empid, empname, salary FROM dbo.Employees WHERE 1 = 1'
SET @Params = '@FilterNameIn NVARCHAR(100), @FilterSalaryIn FLOAT'
IF LEN(@FilterName) > 0
SET @Query = @Query + ' AND empname LIKE @FilterNameIn'
IF LEN(@FilterSalary) > 0
SET @Query = @Query + ' AND salary > @FilterSalaryIn'
IF @OrderField = 0
SET @Query = @Query + ' ORDER BY empname'
IF @OrderField = 1
SET @Query = @Query + ' ORDER BY salary'
IF @OrderField = 2
SET @Query = @Query + ' ORDER BY empid'
EXEC sp_executesql @query, @Params, @FilterNameIn = @FilterName, @FilterSalaryIn = @FilterSalary
SELECT empid, empname, salary FROM dbo.Employees WHERE
empname LIKE CASE WHEN LEN(@FilterName) > 0 THEN @FilterName ELSE empname END
salary >= ISNULL(@FilterSalary, 0)
CASE WHEN @OrderField = 0 THEN empname END,
CASE WHEN @OrderField = 1 THEN salary END,
CASE WHEN @OrderField = 2 THEN empid END
Initially I tried
WHEN @OrderField = 0 THEN empname
WHEN @OrderField = 1 THEN salary
WHEN @OrderField = 2 THEN empid
To order by only one column but since the fields have different data types SQL gave an error. I could do a CAST but salary as NVARCHAR doesn't give good results... So I used the 3 columns sort.....
In both cases the execution plan is very similar, the CASE statement has a Compute Scalar with 0% (from the CASE statement)...
But if I order by the 3rd column ( = 2) to non dynamic statement, according to execution plan comparing both, is much "heavier" since it orders by 2 NULL columns and only then by the desired column....
Is this a case where Dynamic SQL can be used, where it's better than "regular" SQL statements?
If you need to work better, try working less...