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

SQL Server Development (2000)

Dynamic Order By

Author

Topic

robo
Starting Member

2 Posts

Posted - 2001-12-06 : 09:56:22

Hold it! I have searched all over this site and others for an example/solution and have found none that uses a variable for the ORDER BY. I am trying to build a dymnamic SQL statement that will let me do my always changing ORDER BY. The ORDER BY is being passed from an ASP page and could have anywhere from 1 to 5 fields. So I really need to be able to dynamically have it happen. If I can. Please see my code below.

CREATE PROCEDURE SP_GetNormalSearch
@projectid numeric(10),
@comp int,
@searchcrit nvarchar(100)
AS
declare @SQL nvarchar(2000)
set @SQL = ' Select * from GroopeProjectTasks INNER JOIN Category on Category.ID = GroopeProjectTasks.TaskCategory Where
ProjectID = ' + @projectid + ' ORDER BY ' + @searchcrit
exec(@SQL)
GO

Thanks!

robo
Starting Member

2 Posts

Posted - 2001-12-06 : 10:04:35

Nevermind I got it! See this article
http://www.4guysfromrolla.com/webtech/sqlguru/q083099-1.shtml
For whatever reason I needed to convert my values.

CREATE PROCEDURE SP_GetNormalSearch
@projectid numeric(10),
@comp int,
@searchcrit nvarchar(100)

AS

declare @SQL nvarchar(2000)

set @SQL = ' Select * from GroopeProjectTasks INNER JOIN Category on Category.ID = GroopeProjectTasks.TaskCategory Where
ProjectID = ' + convert(nvarchar,@projectid) + ' ORDER BY ' + convert(nvarchar,@searchcrit)
exec(@SQL)
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources