Dynamic 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

Transact-SQL (2000)

Dynamic Order By ..

Author

Topic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-08-22 : 01:45:09

Hello All,

I want to specify the Order By Clause dynamically in my store procedure depending upon the codition.

I have written the following code but its giving me error..

Code:

Declare @OrderBy int
Set @OrderBy = 2
Select Top 10 * From Employee Order By
Case When @OrderBy = 1 Then EmpID
When @OrderBy = 2 Then EmpNameE End

Error
Syntax error converting the varchar value 'ABC -XYZ.. ' to a column of data type int.

but when i Set @OrderBy = 1 then its work correctly..

EmpId is the integer columns and EmpNameE is the Varchar.. columns..

Thanks..

Complicated things can be done by simple thinking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-22 : 02:15:19

Try this

Declare @OrderBy int
Set @OrderBy = 2
Select Top 10 * From Employee Order By
Case When @OrderBy = 1 Then convert(varchar,EmpID)
When @OrderBy = 2 Then EmpNameE End

Madhivanan

Failing to plan is Planning to fail

kapilarya
Yak Posting Veteran

86 Posts

Posted - 2005-08-22 : 02:16:45

try this

Declare @OrderBy int
Set @OrderBy = 1
Select Top 10 * From Employee
Order By
Case When @OrderBy = 2 Then empcode
When @OrderBy = 1 Then right('000000000' + convert(varchar,empid),9) End

Kapil Arya

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-08-22 : 02:18:47

Thanks guys..

yours query worked for me.. :-)

Complicated things can be done by simple thinking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources