I'm working on a stored procedure that will accept a column name from a web application.  The stored procedure will use the @SortColumn to sort the results of the query.  I'm receiving ambiguous column errors when the column values are passed in to the query.  The original query is written using dynamic sql but I created a simple query to reproduce the issue.   Can someone review the sample below and let me know if there is a way to resolve the error. 

In the example below, the Over(Order by Emp_ID) section is causing the error. The order by is expecting e.Emp_ID as the order by value.  Is there anyway for me to rewrite the logic to allow the correct column alias to be using in the order by statement?  Ultimately, I would like to pass in a paramter for the order by column.  Ex.   Over(Order by ' + @SortColunm + '

SELECT RowNum
	,Emp_ID
	,First_Name
	,Last_Name
From (Select Distinct ROW_NUMBER() OVER(Order by Emp_ID) as RowNum, 
e.Emp_ID as Emp_ID
	,e.First_Name as First_Name
	,e.Last_Name as Last_Name
FROM Employee e
LEFT OUTER JOIN Team t ON e.team_id = t.team_Id AND e.Emp_id = t.Emp_Id
LEFT OUTER JOIN AccrualType at ON e.Accrual_Type = at.Type
LEFT OUTER JOIN ClosingProcess cp ON e.Emp_id = cp.Emp_ID 
where Last_Name like 's%'
) As Employees
 
order by Emp_ID 

• Edited by James_P Friday, March 27, 2015 3:58 PM

Sorry, I corrected it.

• Edited by James_P Friday, March 27, 2015 3:59 PM

I've updated my query to use a cte and also included the Row_Number logic.  I'm now receiving "Invalid column name 'RowNum".  Can anyone explain what I'm doing wrong in the code below?   I'm trying to create a stored procedure that accepts a parameter to handle paging and sorting.  The dynamic sql is needed for the SortExpression.

 declare @SortExpression varchar(50) = 'Last_Name';
declare @DynSql varchar(max)=''; 
declare @Emp_ID NVARCHAR(50) = NULL
declare @First_Name VARCHAR(50) = NULL
declare @Last_Name VARCHAR(50) = 's'
declare @StartIndex INT
declare @MaximumRows INT
 
set @DynSql=
'
With Employees as 
( Select Distinct  
e.Emp_ID as Emp_ID
	,First_Name
	,Last_Name
FROM Employee e 
LEFT OUTER JOIN Team t ON e.team_id = t.team_Id AND e.Emp_id = t.Emp_Id
LEFT OUTER JOIN AccrualType at ON e.Accrual_Type = at.Type
LEFT OUTER JOIN ClosingProcess cp ON e.Emp_id = cp.Emp_ID 
 )
  SELECT ROW_NUMBER() Over (Order By ' + @SortExpression + ') As RowNum,
	Emp_ID
	,First_Name
	,Last_Name
	 
FROM  Employees
 WHERE RowNum BETWEEN ' + CAST(@StartIndex as varchar(10)) + ' AND ' + '(' + CAST(@StartIndex as varchar(10))+ CAST(@MaximumRows as varchar(10))+ ') - 1 '
If @Emp_ID is not null
	Set @DynSql = @DynSql + ' And (Emp_ID = @Emp_ID)'
If @First_Name is not null
	Set @DynSql = @DynSql + ' And (First_Name = @First_Name)'
If @Last_Name is not null
	Set @DynSql = @DynSql + ' And (Last_Name = @Last_Name)'
exec (@DynSql) 

• Edited by James_P Friday, March 27, 2015 8:53 PM

columns are meta data. We do not pass meta data as a parameter. Remember Software Engineering 101? Do the words coupling and cohesion mean anything to you? A corrected written module of code has one and only one entry point, one and only one exit point, and performs one and only one clearly defined task. You want to write a "automobiles, squids and Lady Gaga" module! This is not just bad SQL; this is bad programming in any language.