Sending sortExpression as sql command parameter?

[JJ]

When running an sql stored procedure, is it possible to pass in the
sortexpression as a parameter? When I try it all the other parameters
operate as expected, but the rows are not sorted. The paging works ok, but
not the sorting.
JJ

My data layer has something like:

SqlCommand cmd = new SqlCommand("Stored_Proc_Name", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@LowerBound", SqlDbType.Int).Value = lowerBound;
cmd.Parameters.Add("@UpperBound", SqlDbType.Int).Value = upperBound;
cmd.Parameters.Add("@SortExpression", SqlDbType.NVarChar).Value =
sortExpression;
cn.Open();


The SQL stored proc would be something like:

SELECT * FROM(
SELECT
...Table rows....
ROW_NUMBER() OVER (ORDER BY @SortExpression DESC) AS RowNum
FROM
...TheTable...
WHERE
....SelectionCriteria...
) AllRows
WHERE AllRows.RowNum BETWEEN @LowerBound AND @UpperBound
ORDER BY RowNum ASC

 

[JJ]

Hmmm I found my answer here
http://www.asp.net/learn/dataaccess/tutorial26cs.aspx?tabid=63
parameters are not allowed in 'ORDER BY ...' expression in sql.

JJ

 

[Lloyd Sheen]

When running an sql stored procedure, is it possible to pass in the
sortexpression as a parameter? When I try it all the other parameters
operate as expected, but the rows are not sorted. The paging works ok, but
not the sorting.
JJ

My data layer has something like:

SqlCommand cmd = new SqlCommand("Stored_Proc_Name", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@LowerBound", SqlDbType.Int).Value = lowerBound;
cmd.Parameters.Add("@UpperBound", SqlDbType.Int).Value = upperBound;
cmd.Parameters.Add("@SortExpression", SqlDbType.NVarChar).Value =
sortExpression;
cn.Open();


The SQL stored proc would be something like:

SELECT * FROM(
SELECT
..Table rows....
ROW_NUMBER() OVER (ORDER BY @SortExpression DESC) AS RowNum
FROM
..TheTable...
WHERE
...SelectionCriteria...
) AllRows
WHERE AllRows.RowNum BETWEEN @LowerBound AND @UpperBound
ORDER BY RowNum ASC

That will not work. The only way would be to pass a value indicating which
column and which direction to sort. You would then have varieties of same
statement in a stored proc which reads the sort parameter and executes the
correct SQL. If you use dynamic SQL you will loose the advantage of SP's
having a stored plan.

LS

 

[Mark Rae]

Hmmm I found my answer here
http://www.asp.net/learn/dataaccess/tutorial26cs.aspx?tabid=63
parameters are not allowed in 'ORDER BY ...' expression in sql.

You can build up your SQL dynamically within the stored procedure and then
run it with sp_executesql:
http://msdn2.microsoft.com/en-us/library/ms188001(SQL.90).aspx

However, I can't recommend that method because you lose the precompilation /
execution plan advantage of using a stored procedure...

It will, though, allow you to pass in an order by clause as a parameter...