order by @sortby - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

order by @sortby

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-01-10 : 02:15:28

I have a stored procedure

I want to pass in a @sortby which will be the field I want to sort the query by.

i'm getting an error when executing my alter stored procedure

Msg 1008, Level 16, State 1, Procedure spimages, Line 19
The SELECT item identified by the ORDER BY number 1 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.

what am i doing wrong




ALTER PROCEDURE [dbo].[spimages]
	-- Add the parameters for the stored procedure here
@datesearch datetime,
@site varchar(10),
@accuracy int,
@sortby varchar(10)	
AS
BEGIN
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select * from images where date>=@datesearch and date<@datesearch+1 and accuracy<=@accuracy and site=@site order by @sortby
return
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 02:57:11

You need to use dynamic SQL if you want to sort by variable

DECLARE @Sql varchar(8000)
SET @Sql='select * from images where date>=' + @datesearch +' and  date<' + DATEADD(d,1,@datesearch) + ' and accuracy<=' +@accuracy + ' and site=' + @site+' order by ' + @sortby

EXEC(@Sql)

or use CASE expresion

select * from images where date>=@datesearch and date<@datesearch+1 and accuracy<=@accuracy and site=@site order by
CASE
WHEN @sortby=<val1> then field1
WHEN @sortby=<val2> then field2
...
END

But if you use case you should make sure all fields used for sorting are of same datatype.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-10 : 03:23:04

Make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2008-01-10 : 03:25:47

If you use
select * from images where date>=@datesearch and date<@datesearch+1 and accuracy<=@accuracy and site=@site order by
CASE WHEN @sortby=<val1> then field1 END ,
CASE WHEN @sortby=<val2> then field2 END ,
...

then they don't have to be the same datatype.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 04:08:20

quote:
Originally posted by nr

If you use
select * from images where date>=@datesearch and date<@datesearch+1 and accuracy<=@accuracy and site=@site order by
CASE WHEN @sortby=<val1> then field1 END ,
CASE WHEN @sortby=<val2> then field2 END ,
...

then they don't have to be the same datatype.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Yeah thats true. You can use like this too. Sorry didnt think of this option.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources