Dynamic Order by with over 100 combinations

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)

Dynamic Order by with over 100 combinations

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-19 : 09:36:34

Kathy writes "I need to write a dynamic order by clause in my stored procedure which is different from the dynamic order by I have read of yours. This will be generated by the user using 10 different fields to sort by. The search form I am writing is designed so the user has a list box in which they select the order of their sort which goes into another list box. The query is then sorted on the order in which they have selected in the list box. This could come up with over 100 combinations of sorting the ten fields. Is this possible without hard coding and tranlating using the "order by case when ". I have not found a way yet. After the order by, it wants a table field. I tried writing a function to call at this point to translate this but so far it has not worked. Is that possible???? Please Help me?????? Thanks"

Nazim
A custom title

1408 Posts

Posted - 2002-06-19 : 10:06:19

Hi Kathy,

if you are looking for simplicity over performance. then you can use dynamic sql to do it.

eg:

Create procedure OrderingPro @orderbycol varchar(255)
as
declare @sql varchar(800)

set @sql= 'select col1,col2,col3 from tablename order by ' + @orderbycol

exec (@sql)

HTH

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Kathy Schmid
Starting Member

3 Posts

Posted - 2002-06-20 : 10:55:36

Here is my procedure and it is still not working. I am wanting to use a dynamic procedure which the user selects any combination for the search and also searches with a dynamic order by and sort.

Alter Procedure sp_DynamicSearchOrderBy
(
@RecordNumber int=null,
@Last_Name [varchar] (30)=null,
@First_Name [varchar](30)=null,
@Gender [varchar](10)=null,
@Race [varchar](10)=null,
@orderby [varchar](30)=null,
@sort [varchar](10)=null,
@maxrows int=0)

as

declare @sql varchar(1000)

set Rowcount @maxrows

set @sql='SELECT juvSysID AS [Record Number],
juvNameLast AS [Last Name], juvNameFirst AS [First Name],
juvPrimaryRace AS Race, juvGender AS Gender
FROM dbo.dbo_tblJuvenile

where juvSysID =coalesce(@RecordNumber,juvSysID)
and juvNameLast =coalesce(@Last_Name,juvNameLast)
and juvNamefirst=coalesce(@First_Name,juvNameFirst)
and juvGender=coalesce(@Gender,juvGender)
and juvPrimaryRace=coalesce(@Race,juvPrimaryRace)

order by' + @orderby + @sort

exec(@sql)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2002-06-20 : 11:07:48

order by' + @orderby + @sort

you need a space in here such as

order by' + @orderby + ' ' + @sort

Peace

Rick

Kathy Schmid
Starting Member

3 Posts

Posted - 2002-06-20 : 11:17:46

quote:

order by' + @orderby + @sort

you need a space in here such as

order by' + @orderby + ' ' + @sort

Peace

Rick

I am getting an error which says I need to Declare the @RecordNumber which I have alreay declared in the top of my procedure. It seems it does not like the variable in my where statement. I have set the code there to accept just one user input variable or any combination of user input. Any other suggestions????? All help appreciated!!!

colinm
Yak Posting Veteran

62 Posts

Posted - 2002-06-20 : 11:30:03

@RecordNumber is outside the scope of the dynamic SQL

exec(@sql) runs in a separate batch, and so when looking for the variables you have declared it can't find them.
You should add these to the start of your @sql string.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-20 : 11:34:04

colinm is exactly right.

You could do something like...


...
where juvSysID =case when ' + isnull(@RecordNumber,'47') + '= 47 then juvSysID else ' + @RecordNumber + ' end
...

You'll have to double check my quotes, but simplying printing your @sql before the execute should make it pretty clear.

<O>

Edited by - Page47 on 06/20/2002 11:34:44

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources