Dynamic SQL - 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

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL

Author

Topic

renjithgr
Starting Member

7 Posts

Posted - 2008-09-19 : 02:15:00

Need to select records from a database file according to the sort order given here

sort order desciption
___ Name
___ Age
___ Address
___ Place

How to sort these according to user enters…

I think its through dynamic SQL on which I am not confident.

For example
If I select name as 3, age 2, address 4 and place 1 I need to sort in order place,age,name,address

If I select name as 2, age 4, address 1 and place 3 I need to sort in order address,name,place, age.

Whether anybody can help me in this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-19 : 02:36:49

[code]
ORDER BY
case when @sort_name = 1 then Name
when @sort_addr = 1 then Addresss
when @sort_Place = 1 then Place
end,
case when @sort_age = 1 then Age
end,
case when @sort_name = 2 then Name
when @sort_addr = 2 then Addresss
when @sort_Place = 2 then Place
end,
case when @sort_age = 2 then Age
end,
case when @sort_name = 3 then Name
when @sort_addr = 3 then Addresss
when @sort_Place = 3 then Place
end,
case when @sort_age = 3 then Age
end,
case when @sort_name = 4 then Name
when @sort_addr = 4 then Addresss
when @sort_Place = 4 then Place
end,
case when @sort_age = 4 then Age
end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

renjithgr
Starting Member

7 Posts

Posted - 2008-09-19 : 03:01:54

But I am having 16 fields with me for sorting. For understanding I just shown 4. SO if I using this way it will be large code. and my front end is ADK in AS/400 which will not allow to go throgh this much code.

So can you suggest me the better way for this.

Regards,
Renjith

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-19 : 03:18:35

in that case maybe use Dynamic SQL
see http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

renjithgr
Starting Member

7 Posts

Posted - 2008-09-19 : 03:29:08

In the example it specifying the following fields.

@sort_name
@sort_addr
@sort_Place
@sort_age

What this points to. If I am using order by I need to specify the file fields only. Then how I can use this fields mentioned. Can you help me on this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-19 : 03:32:36

Those variable are for the query i suggested previously and since you have 16 fields and that method is too much coding for you then I suggest to use the Dynamic SQL. And if you are using Dynamic SQL then just form your query in the string and use exec() of sp_executesql to execute it.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources