By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,214 Members | 2,072 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,214 IT Pros & Developers. It's quick & easy.

Sending sortExpression as sql command parameter?

P: n/a
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
Jun 8 '07 #1
Share this Question
Share on Google+
3 Replies


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

JJ

"JJ" <ab*@xyz.comwrote in message
news:e$**************@TK2MSFTNGP04.phx.gbl...
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

Jun 8 '07 #2

P: n/a

"JJ" <ab*@xyz.comwrote in message
news:e$**************@TK2MSFTNGP04.phx.gbl...
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

Jun 8 '07 #3

P: n/a
"JJ" <ab*@xyz.comwrote in message
news:O%****************@TK2MSFTNGP06.phx.gbl...
Hmmm I found my answer here
http://www.asp.net/learn/dataaccess/....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/lib...1(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...
--
http://www.markrae.net

Jun 8 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.