this blog contains information for .net and sql stuffs. You can find various tips and tricks to overcome problem you may be facing in ...

Thursday, February 3, 2011

Paging stored procedure for the SQL server 2000

Before a while ago, I have posted query for the true paging but it does not work in SQL server as ROWNUM is not available in it.

Here, is the sample stored procedure for SQL Server 2000

CREATE PROCEDURE [dbo].[State_Search]

@SearchText nvarchar(50) = null

,@PageNo int

,@PageSize int

,@TotalRecords int OUTPUT

AS

BEGIN

SET NOCOUNT ON;

DECLARE @StartFrom int

SET @StartFrom = @PageSize * (@PageNo - 1);

CREATE TABLE #tmpState (

RowNo int IDENTITY(1,1) PRIMARY KEY,

RowId int NOT NULL

);

INSERT INTO #tmpState(RowId)

SELECT CHECKSUM([State].StateGuid) FROM [State]

WHERE (@SearchText IS NULL OR StateCode LIKE @SearchText OR

StateName LIKE @SearchText OR StateDesc LIKE @SearchText)

SET @TotalRecords = (SELECT COUNT(*) FROM #tmpState);

SELECT

[State].*

FROM #tmpState

INNER JOIN [State] ON CHECKSUM([State].StateGuid) = #tmpState.RowId

WHERE #tmpState.RowNo > @StartFrom AND #tmpState.RowNo <= (@StartFrom + @PageSize)

ORDER BY #tmpState.RowNo;

SELECT @TotalRecords AS TotalRecords;

DROP TABLE #tmpState;

END

No comments: