set dynamically ORDER BY - 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 2000 Forums

SQL Server Development (2000)

set dynamically ORDER BY

Author

Topic

php95saj
Starting Member

43 Posts

Posted - 2002-05-22 : 09:44:10

Is is possible to set ORDER BY dynamically? If yes then what will be the syntax. I want to allow user to select how they want to see the recordset ordered. For example in the search form I have check boxes first name, last name, email, country, registration date etc..
If the user selects last name and email, the values will be passed to the stroed procedure and will append the ORDER BY command at the end of the procedure something like
ORDER BY lastName, email ???
TIA

Sharjeel

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-05-22 : 09:53:15

Build your order by clause on the client side as a string after the form post. Then pass it as an argument to the stored proc (you are using stored procedures arent you

). Your sproc would look something like...


create proc SearchTable
@OrderBy varchar(500)
as
set nocount on
declare @SQL varchar(2000)
set @SQL = 'select * from foo '
set @SQL = @SQL + @OrderBy
exec(@SQL)

You could just pass the column names as arguments but then you would have to test for multiple columns to order by, IMO it would be easier to build the order by on the client.

hth,
Justin

Edited by - justinbigelow on 05/22/2002 09:54:05

php95saj
Starting Member

43 Posts

Posted - 2002-05-22 : 09:57:30

Thanks Justin
But it is really important that I build the query within the stored procedure as I have the whole procedure being built depending upon what the user selected. I mean what fields they wanted to search on.
Which I have got but all I want is to make it more flexible for the user to be able to select what order they want to view the results.

Thanks

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-05-22 : 09:58:27

Sounds like you need to do a search on the site for
dynamic sql

Michael

Nazim
A custom title

1408 Posts

Posted - 2002-05-22 : 10:01:11

Did you read this artilce on "Dynamic Order by" written by Garth
http://www.sqlteam.com/item.asp?ItemID=2209 .

HTH

--------------------------------------------------------------

Edited by - Nazim on 05/22/2002 10:02:01

php95saj
Starting Member

43 Posts

Posted - 2002-05-22 : 10:07:06

Thanks all for the response. I got the answer from Justin's earlier mail plus the article mentioned by Nizam.
Good team work
Cheers all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources