Passing parameter to Stored Procedure's Order By

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's Order By

Author

Topic

alex3wworld
Starting Member

1 Post

Posted - 2003-09-15 : 11:41:24

I am trying to implement a "Sorting" capability for my stored prosedure :
CREATE PROCEDURE TestSorting
(
@sortExpression nvarchar(20)
)
AS

SET NOCOUNT ON

SELECT*
FROM Customers
ORDER BY @sortExpression

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO
The problem is an error which I am getting while trying to save it:
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

So,my question is: How to use parameter together with "ORDER BY" clous?
Alex.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-15 : 12:03:15

You will want to use a CASE statement for your ORDER BY. You can not use a parameter in the ORDER BY as the error mentions. You can do it through dynamic sql, but that is not the recommended approach since it will affect performance. The recommended approach is to use a CASE statement. Do searching on this site for the CASE statement in an ORDER BY and you'll see just how to do it.

Tara

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2003-09-15 : 12:04:33

You have to use dynamic spl for this. See sp_execute_sql in BOL for details on building the statement dynamically, then executing it.

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-09-15 : 12:05:21

Use dynamic SQL.

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

SET NOCOUNT ON

DECLARE @SQL VARCHAR(2000)
SET @SQL = ''

SET @SQL = @SQL + 'SELECT * FROM Customers ORDER BY ' + @sortExpression

EXEC (@SQL)
SET @SQL= ''
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO

__________________
Make love not war!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-15 : 12:10:05

Dynamic sql does not need to be run in order to fix this. It can be accomplished by changing the select statement. Using a CASE statement will usually fix the problem.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources