sp_executeSQL with Dynamic ORDER BY

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

sp_executeSQL with Dynamic ORDER BY

Author

Topic

Adelyra
Starting Member

14 Posts

Posted - 2010-11-15 : 13:45:55

Hello!

I'm still pretty new to working with Stored Procedures as well as sp_executeSQL and I'm stuck on how to write dynamic ORDER BY statements in a stored procedure. Everything I find in other forums and websites is way too cryptic and I don't understand any of it.

In my site I have a large table with many columns. The data in this table is generating from the database and when the user clicks on a column heading it dynamically sorts by that column (both ASC and DESC).

At the moment everything is done via Dynamic SQL embeded within the code itself by putting the column title into the querystring, the code picks up the querystring value and 'ORDERS BY' that value.

So first of all, where do I create this sp_executeSQL statement? Do I create it in the same way I would create a normal stored procedure and then call the execute command in my code passing in the appropriate parameters?

Really, it would be so easy if I could just write

CREATE PROCEDURE [owner].[procedure_name]
     @orderBY varchar(100)
AS
     SELECT itemID, itemName FROM mainTable ORDER BY @orderBy
GO

--Then...

EXECUTE owner.procedure_name 'itemID ASC'

Now, obviously that's not possible but, just to give you an idea of what I'm looking for...

Please let me know if you need more information. I'm not certain what else would help.

Thanks so much!

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-15 : 13:55:51

What language is the site written in?

Could you handle the sorting in the front end, rather than the database?

Adelyra
Starting Member

14 Posts

Posted - 2010-11-15 : 14:15:27

quote:
Originally posted by TimSman

What language is the site written in?

Could you handle the sorting in the front end, rather than the database?

Sorry, it's written in VBScript (ASP). As for sorting in the front end, how do you mean? I'm definitely open to suggestions!

The reason I want to switch to stored procedures is not only for performance but I'm also having issues with sql statemtents mixing together and causes 500 errors. For example, when someone tries to update a task in the edit task page they'll get a 500 error -- not all the time though. Then, when I look in the database, I can see that there's a lock on a process that's trying to execute this sql command:

select * from UPDATE mainTable SET column1='value1'... etc.

I think this may have to do with all of the dynamic sql statments being cached either in the page or the database. My first attempt to fix it was going through all 7 pages and setting every single variable to nothing or "". My second attempt is to convert all of the sql to stored procedures.

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-15 : 14:23:42

Classic ASP? I can't help there. If it's .Net, google "sortable collection .net".

As for doing it in a stored procedure, look here:

[url]http://www.sqlteam.com/article/dynamic-order-by[/url]

Adelyra
Starting Member

14 Posts

Posted - 2010-11-16 : 08:01:56

quote:
Originally posted by TimSman

Classic ASP? I can't help there. If it's .Net, google "sortable collection .net".

As for doing it in a stored procedure, look here:

[url]http://www.sqlteam.com/article/dynamic-order-by[/url]

Thanks for the article recommendation!

I've followed a procedure that was in that article and I'm getting a syntax error that I'm not sure of how to fix. Here's the code for the stored proc:

CREATE PROCEDURE [owner].[usp_OrderBy]
	@orderBy varchar(100) = NULL
AS
	SELECT ID, Status, PostedBy, Assigned, Subject
	FROM tableName
	ORDER BY	CASE
				WHEN @orderBy = 'ID' THEN ID
				WHEN @orderBy = 'Status' Then Status
				WHEN @orderBy = 'PostedBy' Then PostedBy
				WHEN @orderBy = 'Assigned' Then Assigned
				WHEN @orderBy = 'Subject' Then Subject
				ELSE ID
			END
GO

--Then in Query Analyzer:

EXEC owner.usp_OrderBy 'Status'

When I try to execute this code it returns a syntax error:

Server: Msg 245, Level 16, State 1, Procedure usp_OrderBy, Line 4
Syntax error converting the varchar value 'Closed' to a column of data type int.

Does anyone know why I'm getting this error?

I'd also like to know why, in the code posted above, I can't add ASC or DESC to the end of the case statements. That also returns a syntax error.

ex:

CASE
	WHEN @orderBy = 'ID' THEN ID ASC
	WHEN @orderBy = 'Status' Then Status DESC
	WHEN @orderBy = 'PostedBy' Then PostedBy DESC
	WHEN @orderBy = 'Assigned' Then Assigned ASC
	WHEN @orderBy = 'Subject' Then Subject ASC
	ELSE ID ASC
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-16 : 10:13:21

It is becuase the columns listed in the Order by clause have different datatypes. You need to convert them to varchar

Madhivanan

Failing to plan is Planning to fail

Adelyra
Starting Member

14 Posts

Posted - 2010-11-16 : 11:02:54

quote:
Originally posted by madhivanan

It is becuase the columns listed in the Order by clause have different datatypes. You need to convert them to varchar

Madhivanan

Failing to plan is Planning to fail

Hi Madhivanan. Thanks for your reply!

Here's the fix I've implemented however it's still buggy:

CASE
	WHEN @orderBy = 'ID' THEN CAST(ID AS varchar(20))
	WHEN @orderBy = 'Status' Then Status
	WHEN @orderBy = 'PostedBy' Then PostedBy
	WHEN @orderBy = 'Assigned' Then Assigned
	WHEN @orderBy = 'Subject' Then Subject
	ELSE CAST(ID AS varchar(20))
END

This is still buggy because, although it sorts all the columns perfectly, it sorts the ID column alphabetically instead of chronologically. For example, instead of returning
ID:
1
2
3
4
...
111
112
113...etc.

it returns
ID:
10
101
102
103
104
105
106
107
108
109
11
111
112
113
114
115...etc

So it's organizing the numbers as if they were strings instead of integers.

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-16 : 11:08:46

Try this:

CASE WHEN @orderBy = 'ID' THEN ID ELSE NULL END
CASE WHEN @orderBy = 'Status' Then Status ELSE NULL END
CASE WHEN @orderBy = 'PostedBy' Then PostedBy ELSE NULL END
CASE WHEN @orderBy = 'Assigned' Then Assigned ELSE NULL END
CASE WHEN @orderBy = 'Subject' Then Subject ELSE NULL END

Adelyra
Starting Member

14 Posts

Posted - 2010-11-16 : 11:32:17

quote:
Originally posted by TimSman

Try this:

CASE WHEN @orderBy = 'ID' THEN ID ELSE NULL END
CASE WHEN @orderBy = 'Status' Then Status ELSE NULL END
CASE WHEN @orderBy = 'PostedBy' Then PostedBy ELSE NULL END
CASE WHEN @orderBy = 'Assigned' Then Assigned ELSE NULL END
CASE WHEN @orderBy = 'Subject' Then Subject ELSE NULL END

This worked perfectly! Thanks so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources