Passing "ORDER BY" parameter causes error

Hi everyone,

I want to be able to pass an "ORDER BY" parameter into my stored procedure but I am receiving errors when I do. For example:

CREATE PROCEDURE GetFromTable
(
@.SortOn varchar(20)
)
AS
SELECT *
FROM Table
ORDER BY @.SortOn
GO

This is the error I get: Variables are only allowed when ordering by an expression referencing a column name. Any suggestions on what I can do to make this work?

Thanks in advance,

PietWhy?

This is bad on so many levels...why not go dynamic crazy?

USE Northwind
GO

CREATE PROC mySproc99
@.TABLE_NAME sysname, @.WHERE varchar(2000), @.ORDERBY varchar(2000)
AS
BEGIN
DECLARE @.sql varchar(8000)
SELECT @.sql = 'SELECT * FROM ' + @.TABLE_NAME
+ ' WHERE ' + @.WHERE
+ ' ORDER BY ' + @.ORDERBY
EXEC(@.sql)
END
GO

EXEC mySproc99 'Orders','OrderId > 0','ShippedDate'
GO

DROP PROC mySproc99
GO|||Brilliant code! Henceforth, the only stored proc in my databases shall be this "one-size-fits-all" piece of code. However, I think I may make it even more universal like this:

USE Northwind
GO

CREATE PROC mySproc99
@.SQLStatement(4000)
AS
BEGIN
EXEC(@.SQLStatement)
END
GO

EXEC mySproc99 'select * from Orders where OrderId > 0 order by ShippedDate'
GO

DROP PROC mySproc99
GO

Much more functional and versatile, wouldn't you agree? The only stored procedure you will ever need!|||blindman,

Are you for reals?

Henceforth, the only stored proc in my databases shall be this "one-size-fits-all" piece of code.

Or, are you being a bit facetious?

Thanks for the help guys. Going dynamic makes things a bit easier for sure.

Piet|||I'm suspecting the BRETT was being facetious...|||What meaning does "This is bad on so many levels" carry?|||Piet:

When Brett wrote "This is bad on so many levels..." he was trying to convey the idea that dynamic code is potentially a bad thing, and when used in this way (to dynamically change the order of the results), it is worse than usual.

Dynamic code opens up all kinds of possibilities for problems like SQL Injection, etc. If the user has any control over what string gets passed into your dynamic SQL, you've basically given them the keys to the kingdom as far as what they can do.

Both Brett and Blindman were attempting to show some of the problems that can arise from dynamic code. While there are a few uses for dynamic code (and heaven knows I seem to post a lot of it), they are really few and far between for production code. Dynamic SQL belongs in the DBA's bag of tricks, but you need to be very stingy about using it... It should only be as a last resort, and no part of the dynamic SQL should EVER be controlled by the end user.

-PatP|||You should think about why you are asking the database to custom-order results in the first place. Ordering the results may stray into the realm of data-presentation, and would then be better left to the interface to handle.

What interface, dare I ask, are you using?|||Many years ago (8?) I was experimenting with procedure subscripts in 6.5, and actually employed this rarely used feature in one of my apps. It may sound inefficient, but it is by far much better than resorting to dynamic SQL:

create proc myproc;1 as
select * from pubs.dbo.authors order by 1
go
create proc myproc;2 as
select * from pubs.dbo.authors order by 2
go
create proc myproc;3 as
select * from pubs.dbo.authors order by 3
go
create proc myproc;4 as
select * from pubs.dbo.authors order by 4
go

Then all your FE needs to do is to make determination which subscript to append to the call of the proc to get the results in the specific order.|||Blindman,

Good question and I think you're right that I could probably take care of the sorting in a Dataview. I am using a repeater in a web form app using c#.

Thanks for the reminder.

Piet