Dynamical order by Stored Procedure

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 2008 Forums

Transact-SQL (2008)

Dynamical order by Stored Procedure

Author

Topic

hari4u6
Starting Member

12 Posts

Posted - 2009-12-23 : 01:43:59

Hello all,
I've to implemented sorting in the sp based on the parameter passed..
My sp is
ALTER Procedure [dbo].[STC_GetForumPosts]
(
@ForumThreadID int
)
as
Begin
select R.ForumThreadID,R.ThreadSubject,R.Description,U.UserName from ForumReply R,Users U
where R.ForumThreadID=@ForumThreadID and R.PostedBY=U.UserId
order by R.PostDate
End

where i have to set the order by asec or desc based on the value

order by parameter is R.Postdate all the time

Hari.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-23 : 01:56:46

U can use Dynamic Sql

Declare @query varchar(max)
Declare @order_by varchar(10)

if(....)
set @order_by='desc'

else

set @order_by=''

set @query=' select R.ForumThreadID,R.ThreadSubject,R.Description,U.UserName from ForumReply R,Users U
where R.ForumThreadID=@ForumThreadID and R.PostedBY=U.UserId
order by R.PostDate '+ @order_by

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

hari4u6
Starting Member

12 Posts

Posted - 2009-12-23 : 02:47:57

thanks for the reply,

After this how to execute the query..

Am a newbie to sql , dont mind if this is silly question.

Hari.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-23 : 03:13:51

exec STC_GetForumPosts 3 ---passthe value ForumThreadID

hari4u6
Starting Member

12 Posts

Posted - 2009-12-23 : 03:21:17

but when i execute sp am getting command success message

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-23 : 03:33:17

in ur sp u have to keep
exec(@query)
in the last line
and after altering the sp run the exec STC_GetForumPosts 3

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-23 : 03:35:24

Sorry forget to add last line..

exec(@query)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

hari4u6
Starting Member

12 Posts

Posted - 2009-12-23 : 04:28:27

Thank you for the reply,
now my sp is

ALTER Procedure [dbo].[STC_GetForumPosts]
(
@ForumThreadID int,
@OrderBy int
)
as
Begin
Declare @query varchar(max)
Declare @order_by varchar(10)

if(@OrderBy=1)
set @order_by='desc'
else
set @order_by=''

set @query=' select R.ForumThreadID,R.ThreadSubject,R.Description,U.UserName from ForumReply R,Users U where R.ForumThreadID=@ForumThreadID and R.PostedBY=U.UserId order by R.PostDate '+ @order_by
exec(@query)
End

but when i execute am getting error as

Must declare the scalar variable "@ForumThreadID".

Hari.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-23 : 04:42:59

Modify it like this

set @query=' select R.ForumThreadID,R.ThreadSubject,R.Description,U.UserName from ForumReply R,Users U where R.ForumThreadID= '+ cast(@ForumThreadID as varchar(25))+' and R.PostedBY=U.UserId order by R.PostDate '+ @order_by

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-23 : 04:53:17

If you *must* use dynamic sql for this then AT LEAST have the good sense to use sp_executeSql. That way you'll get a cached plan. Try this


ALTER PROCEDURE [dbo].[STC_GetForumPosts]
( 
	@ForumThreadID INT
	, @OrderBy INT
)
AS BEGIN

	DECLARE @query NVARCHAR(MAX)
	DECLARE @order_by NVARCHAR(10)

	IF ( @OrderBy=1 ) SET @order_by=N'DESC' ELSE SET @order_by=N'ASC'

	SET @query = N'
	SELECT
		R.ForumThreadID
		, R.ThreadSubject
		, R.Description
		, U.UserName
	FROM
		ForumReply R
		JOIN Users U ON U.UserId = R.PostedBY
	WHERE
		R.ForumThreadID=@ForumThreadID
	ORDER BY
		R.PostDate '+ @order_by

	EXEC sp_executeSql
		@query 
		, N'@forumThreadID INT'
		, @forumThreadID

END

NB : Edited for typos
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

hari4u6
Starting Member

12 Posts

Posted - 2009-12-23 : 04:58:11

Thank you so much..

It resolved my problem

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-23 : 05:19:43

Also make sure you read this article fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources