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
AND
salary >= ISNULL(@FilterSalary, 0)
ORDER BY 
	CASE WHEN @OrderField = 0 THEN empname END,
	CASE WHEN @OrderField = 1 THEN salary END,
	CASE WHEN @OrderField = 2 THEN empid  END

ORDER BY 
	CASE 
         WHEN @OrderField = 0 THEN empname 
	 WHEN @OrderField = 1 THEN salary
	 WHEN @OrderField = 2 THEN empid
END

ORDER BY CASE WHEN @ordfld = 1 THEN MoneyField WHEN @ordfld = 2 THEN NameField END

ORDER BY CASE WHEN @ordfld = 1 THEN MoneyField END, CASE WHEN @ordfld = 2 THEN NameField END

select
col1
,col2
,col3
from tablename
order by 1

CREATE NONCLUSTERED INDEX [idx_Employees_003] ON [dbo].[Employees] 
(
	[empname] ASC,
	[salary] ASC
);

SELECT empid, empname, salary FROM dbo.Employees WHERE empname >= CASE WHEN LEN(@FilterName) > 0 THEN @FilterName ELSE empname END AND salary >= ISNULL(@FilterSalary, 0)