/DotNetKicks/Database/_old/003.DotNetKicks.GetPagedSubmittedStoriesByUserIDAndHostID.SQL.2005.sql

http://dotnetkicks.googlecode.com/ · SQL · 36 lines · 25 code · 10 blank · 1 comment · 0 complexity · a56f25bebb90f84bad79f6ce3bbe11be MD5 · raw file

  1. USE [DotNetKicks]
  2. GO
  3. /****** Object: StoredProcedure [dbo].[Kick_GetPagedSubmittedStoriesByUserIDAndHostID] Script Date: 09/07/2007 17:27:57 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. CREATE PROCEDURE [dbo].[Kick_GetPagedSubmittedStoriesByUserIDAndHostID]
  9. @UserID int,
  10. @HostID int,
  11. @PageNumber int,
  12. @PageSize int
  13. AS
  14. BEGIN
  15. DECLARE @StartRow int, @EndRow int
  16. SET @StartRow = (((@PageNumber - 1) * @PageSize) + 1);
  17. SET @EndRow = (@StartRow + @PageSize - 1);
  18. WITH SubmittedStories
  19. AS (SELECT ROW_NUMBER() OVER (ORDER BY Kick_Story.CreatedOn DESC) AS
  20. Row, dbo.Kick_Story.*
  21. FROM
  22. dbo.Kick_Story
  23. WHERE dbo.Kick_Story.UserID=@UserID AND dbo.Kick_Story.HostID=@HostID)
  24. SELECT * FROM SubmittedStories
  25. WHERE ROW between @StartRow AND @EndRow
  26. END