Monday, April 25, 2011

Sql paging example, paging in sql 2000

Create PROCEDURE spPaggingExample
(
@ProductInfo varchar(100)=null,
@SearchBy varchar(50)=null,
@startRowIndex int,
@maximumRows int,
@totalRows int OUTPUT
)
AS

-- sample spPaggingExample '',null,1,10,0

DECLARE @first_id int, @startRow int

SET @startRowIndex = @startRowIndex * @maximumRows

IF @startRowIndex = 0 SET @startRowIndex = 1

SET ROWCOUNT @startRowIndex

SELECT @first_id = UniqueTableId FROM myProductTable where (Isnull(IsApproved,0)=1 and ProductName like '%'+@ProductInfo+'%' or ProductDescription like '%'+@ProductInfo+'%' ) ORDER BY UniqueTableId

--PRINT @first_id
SET ROWCOUNT @maximumRows
SELECT * FROM myProductTable WHERE Isnull(IsApproved,0)=1 and UniqueTableId >= @first_id and (ProductName like '%'+@ProductInfo+'%' or ProductDescription like '%'+@ProductInfo+'%' ) ORDER BY UniqueTableId

SET ROWCOUNT 0
-- GEt the total rows
SELECT @totalRows = COUNT(UniqueTableId) FROM myProductTable where (Isnull(IsApproved,0)=1 and ltrim(rtrim(ProductName)) like '%'+@ProductInfo+'%' or ProductDescription like '%'+@ProductInfo+'%' )

RETURN @totalRows

No comments:

ETG Consultancy

Web Designing Development Analysis & Promotion
Asp.Net 2.0 SQL WWF WCF SEO Marketing Ajax JQuery NHibernate MVC