Dynamic Order By Clause

I'm trying to dynamically determine the Order By clause, like I would in VB,
but I can't figure out the syntax. This is my SP. Thanks
CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
@.mForumID int,
@.mSearchValue varchar(100),
@.mOrderBy varchar(20)
AS
Declare @.OrderByField as varchar(20)
Select @.OrderByField =
CASE
WHEN @.mOrderBy = "" THEN 'MB_Posts.ID'
WHEN @.mOrderBy = "LastPostDesc" THEN ' MB_Posts.Last_Post DESC'
WHEN @.mOrderBy = "LastPost" THEN ' MB_Posts.Last_Post'
WHEN @.mOrderBy = "AuthorDesc" THEN ' users.UName DESC'
WHEN @.mOrderBy = "Author" THEN ' users.UName'
WHEN @.mOrderBy = "RepliesDesc" THEN 'MB_Posts.Replies DESC'
WHEN @.mOrderBy = "Replies" THEN 'MB_Posts.Replies'
WHEN @.mOrderBy = "TopicDesc" THEN 'MB_Posts.Subject DESC'
WHEN @.mOrderBy = "Topic" THEN ' MB_Posts.Subject'
ELSE 'MB_Posts.ID!'
END
SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
FROM USERS
INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
INNER JOIN Icons ON users.Icon_ID = Icons.ID
WHERE MB_Posts.Display = 1
AND Thread = 0
AND MB_Posts.AdminMsg = 0
AND Forum_ID= @.mForumID
AND Body LIKE '%' + @.mSearchValue + '%'
ORDER BY @.OrderByField
GOHi,
See the below URL:-
http://www.sommarskog.se/dynamic_sql.html#Order_by
Thanks
Hari
SQL Server MVP
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F1BF6BDD-C08C-4EBA-993B-0AF6080516C9@.microsoft.com...
> I'm trying to dynamically determine the Order By clause, like I would in
> VB,
> but I can't figure out the syntax. This is my SP. Thanks
> CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
> @.mForumID int,
> @.mSearchValue varchar(100),
> @.mOrderBy varchar(20)
> AS
> Declare @.OrderByField as varchar(20)
> Select @.OrderByField =
> CASE
> WHEN @.mOrderBy = "" THEN 'MB_Posts.ID'
> WHEN @.mOrderBy = "LastPostDesc" THEN ' MB_Posts.Last_Post DESC'
> WHEN @.mOrderBy = "LastPost" THEN ' MB_Posts.Last_Post'
> WHEN @.mOrderBy = "AuthorDesc" THEN ' users.UName DESC'
> WHEN @.mOrderBy = "Author" THEN ' users.UName'
> WHEN @.mOrderBy = "RepliesDesc" THEN 'MB_Posts.Replies DESC'
> WHEN @.mOrderBy = "Replies" THEN 'MB_Posts.Replies'
> WHEN @.mOrderBy = "TopicDesc" THEN 'MB_Posts.Subject DESC'
> WHEN @.mOrderBy = "Topic" THEN ' MB_Posts.Subject'
> ELSE 'MB_Posts.ID!'
> END
>
> SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
> MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
> FROM USERS
> INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
> INNER JOIN Icons ON users.Icon_ID = Icons.ID
> WHERE MB_Posts.Display = 1
> AND Thread = 0
> AND MB_Posts.AdminMsg = 0
> AND Forum_ID= @.mForumID
> AND Body LIKE '%' + @.mSearchValue + '%'
> ORDER BY @.OrderByField
>
> GO
>|||Order By clause requires a literal string. You might want to take a look at
Erland's article for some info/work around.
http://www.sommarskog.se/dynamic_sql.html#Order_by
-oj
"Phill" <Phill@.discussions.microsoft.com> wrote in message
news:F1BF6BDD-C08C-4EBA-993B-0AF6080516C9@.microsoft.com...
> I'm trying to dynamically determine the Order By clause, like I would in
> VB,
> but I can't figure out the syntax. This is my SP. Thanks
> CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
> @.mForumID int,
> @.mSearchValue varchar(100),
> @.mOrderBy varchar(20)
> AS
> Declare @.OrderByField as varchar(20)
> Select @.OrderByField =
> CASE
> WHEN @.mOrderBy = "" THEN 'MB_Posts.ID'
> WHEN @.mOrderBy = "LastPostDesc" THEN ' MB_Posts.Last_Post DESC'
> WHEN @.mOrderBy = "LastPost" THEN ' MB_Posts.Last_Post'
> WHEN @.mOrderBy = "AuthorDesc" THEN ' users.UName DESC'
> WHEN @.mOrderBy = "Author" THEN ' users.UName'
> WHEN @.mOrderBy = "RepliesDesc" THEN 'MB_Posts.Replies DESC'
> WHEN @.mOrderBy = "Replies" THEN 'MB_Posts.Replies'
> WHEN @.mOrderBy = "TopicDesc" THEN 'MB_Posts.Subject DESC'
> WHEN @.mOrderBy = "Topic" THEN ' MB_Posts.Subject'
> ELSE 'MB_Posts.ID!'
> END
>
> SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
> MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
> FROM USERS
> INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
> INNER JOIN Icons ON users.Icon_ID = Icons.ID
> WHERE MB_Posts.Display = 1
> AND Thread = 0
> AND MB_Posts.AdminMsg = 0
> AND Forum_ID= @.mForumID
> AND Body LIKE '%' + @.mSearchValue + '%'
> ORDER BY @.OrderByField
>
> GO
>|||Thanks for pointing me in the right direction. I have different data types,
so I had to use the second format. The only problem is that it won't sort i
n
DESC order. This is what is looks like now. Any suggestions?
CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
@.mForumID int,
@.mSearchValue varchar(100) =null,
@.mOrderBy varchar(20)=null
AS
IF RIGHT(@.mOrderBy,4)='Desc'
SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
FROM USERS
INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
INNER JOIN Icons ON users.Icon_ID = Icons.ID
WHERE MB_Posts.Display = 1
AND Thread = 0
AND MB_Posts.AdminMsg = 0
AND Forum_ID= @.mForumID
AND Body LIKE '%' + @.mSearchValue + '%'
ORDER BY CASE @.mOrderBy WHEN NULL THEN MB_Posts.ID ELSE NULL END,
CASE @.mOrderBy WHEN 'LastPostDesc' THEN MB_Posts.Last_Post
ELSE NULL END,
CASE @.mOrderBy WHEN 'AuthorDesc' THEN users.UName ELSE
NULL END,
CASE @.mOrderBy WHEN 'RepliesDesc' THEN MB_Posts.Replies
ELSE NULL END,
CASE @.mOrderBy WHEN 'TopicDesc' THEN MB_Posts.Subject ELSE
NULL END
DESC
ELSE
SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
FROM USERS
INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
INNER JOIN Icons ON users.Icon_ID = Icons.ID
WHERE MB_Posts.Display = 1
AND Thread = 0
AND MB_Posts.AdminMsg = 0
AND Forum_ID= @.mForumID
AND Body LIKE '%' + @.mSearchValue + '%'
ORDER BY CASE @.mOrderBy WHEN NULL THEN MB_Posts.ID ELSE NULL END,
CASE @.mOrderBy WHEN 'LastPost' THEN MB_Posts.Last_Post
ELSE NULL END,
CASE @.mOrderBy WHEN 'Author' THEN users.UName ELSE NULL END,
CASE @.mOrderBy WHEN 'Replies' THEN MB_Posts.Replies ELSE
NULL END,
CASE @.mOrderBy WHEN 'Topic' THEN MB_Posts.Subject ELSE
NULL END
ASC
"Phill" wrote:

> I'm trying to dynamically determine the Order By clause, like I would in V
B,
> but I can't figure out the syntax. This is my SP. Thanks
> CREATE PROCEDURE [dbo].[usp_Search_MessageBoard]
> @.mForumID int,
> @.mSearchValue varchar(100),
> @.mOrderBy varchar(20)
> AS
> Declare @.OrderByField as varchar(20)
> Select @.OrderByField =
> CASE
> WHEN @.mOrderBy = "" THEN 'MB_Posts.
ID'
> WHEN @.mOrderBy = "LastPostDesc" THEN ' MB_Posts.Last_Post DESC'
> WHEN @.mOrderBy = "LastPost" THEN ' MB_Posts.Last_Pos
t'
> WHEN @.mOrderBy = "AuthorDesc" THEN ' users.UName DESC'
> WHEN @.mOrderBy = "Author" THEN ' users.UName'
> WHEN @.mOrderBy = "RepliesDesc" THEN 'MB_Posts.Replies DESC'
> WHEN @.mOrderBy = "Replies" THEN 'MB_Posts.Repli
es'
> WHEN @.mOrderBy = "TopicDesc" THEN 'MB_Posts.Subject DESC'
> WHEN @.mOrderBy = "Topic" THEN ' MB_Posts.Subjec
t'
> ELSE 'MB_Posts.ID!'
> END
>
> SELECT MB_Posts.ID, MB_Posts.Forum_ID, MB_Posts.Subject, MB_Posts.UID,
> MB_Posts.Replies, MB_Posts.Last_Post, users.UName, Icons.Image
> FROM USERS
> INNER JOIN MB_Posts ON users.UID = MB_Posts.UID
> INNER JOIN Icons ON users.Icon_ID = Icons.ID
> WHERE MB_Posts.Display = 1
> AND Thread = 0
> AND MB_Posts.AdminMsg = 0
> AND Forum_ID= @.mForumID
> AND Body LIKE '%' + @.mSearchValue + '%'
> ORDER BY @.OrderByField
>
> GO
>