Create Store Procedure to Paging!

Hi All!

I have Store Procedure:

If exists(Select * From sysobjects Where Name like 'Forum_Topic_SelectFromForum')
Drop Procedure Forum_Topic_SelectFromForum
go
CREATE PROCEDURE Forum_Topic_SelectFromForum
(
@.ForumID varchar(10)
)

AS BEGIN TRANSACTION
SELECT * from Forum_Topic whereForumID=@.ForumID Order by Tmp DESC
IF @.@.ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

Now, I want to Add 2 Variables: @.Offset int, @.Count int . With @.Offset: the point of data, @.Count: sum of row will get.

when get data I want it get from @.Offset to Added @.Count.

Help me to rewrite this store procedure. Thanks

Hi duynnh,

Are you using SQL 2005? That makes it really easy. Seethis blog post for an example of how you can use a CTE and the new Row_Number() function.

If you're not using 2005 you can do it by creating dynamic SQL. Check outthis post for a generic 'returnpage' stored procedure. Other options include using a temp table or possibly some trickery involving set rowcount if your schema works nicely with that method. Seehere for a more thorough examination of your options.

I hope that helps.

|||

Try the code below (assuming you are using sql 2005/express). I've tested in my side, it works fine

CREATE PROCEDURE Forum_Topic_SelectFromForum( @.ForumID varchar(10), @.offsetint, @.countint)AS BEGIN TRANSACTIONselect * from(SELECT *,row_number() over( Order by Tmp DESC)as row from Forum_Topic where ForumID=@.ForumID )as testwhere test.row between @.offset and @.offset+@.countIF @.@.ERROR <> 0 ROLLBACK TRANSACTIONELSE COMMIT TRANSACTION