Passing parameter to stored procedure with ORDER BY cl.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Passing parameter to stored procedure with ORDER BY cl.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-22 : 07:34:36

Alex writes "I need to create a stored procedures with sorting caipability,so it would be looks like:

CREATE PROCEDURE sp_MySorting
(
@sortExpression nvarchar(20)
)
AS

SELECT *
FROM My_Table
ORDER BY @sortExpression

GO

But the problem is : when I am trying to save it,I am getting an error:
error 1008
The SELECT item identified by the ORDER BY number %d contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

If I will use a commas around '@sortExpression' then it will just pass the string=@sortExpression,but not variable.
What would be the solution for this problem?"

OMB
Yak Posting Veteran

88 Posts

Posted - 2003-09-22 : 07:47:16

Try This



DROP  procedure sp_MySorting

GO 

create procedure sp_MySorting  @sortExpression nvarchar(20)

As 
declare @select as varchar (200)

SELECT @select = 'SELECT * FROM My_Table ORDER BY' + @sortExpression


EXEC(@select)


go

OMB

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-22 : 08:52:56

be careful of dynamic SQL. search the articles here and the forums for "dynamic ORDER BY" for other ideas.

OMB -- what happens if I execute:

sp_MySorting 'Col1; DROP TABLE My_Table'

?? Also, for users to run this stored procedure, they will need explicit permissions on My_Table which in many cases you don't want them to have, if you wish table access to occur only through stored procedures and views.

- Jeff

OMB
Yak Posting Veteran

88 Posts

Posted - 2003-09-22 : 09:59:25

Jeff

I am assuming that the developer who creted this SP would not give direct acces to the stored proc via Query analyser for example, and that it could only be executed via an app, thus reducing the possibility of such a command like "DROP TABLE My_Table"

But valid point.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources