Murugan.com
Murugan Andezuthu Dharmaratnam

  |  HOME   |  BLOG   |  TWITTER   |  ARTICLES   |  8086  |  C++   |  VC++   |  ASP .NET   |  VB .NET   |  JAVA SCRIPT   |  MS SQL   |  PHP   |  MY   |  VIDEOS   |  DOWNLOADS   |  CONTACT ME   |  



Generic Stored Procedure To Return Get Data From Table With Paging


Home  > MSSQL  > Generic Stored Procedure To Return Get Data From Table With Paging 
       
A Simple Single Generic Stored Procedure to return dataset from any table in a database with paging. 

USE [DFPS]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetPagingData]    Script Date: 09/22/2011 05:00:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_GetPagingData]
@FilterText nvarchar(max) = null,
@PageSize [int] = 10,
@SortOrder nvarchar(max) = N'ID',
@ObjectName nvarchar(50),
@CurrentPage int OUTPUT,
@PageCount int OUTPUT
AS
BEGIN
DECLARE @sqlStr NVARCHAR(max)
DECLARE @Count numeric(12,2)
DECLARE @rowStartIdx int
if @CurrentPage is null
set @CurrentPage = 0

SET @sqlStr = N'SELECT @CountParam = COUNT(*) FROM '+ @ObjectName + CASE WHEN @FilterText IS NOT NULL THEN ' WHERE '+ @FilterText ELSE '' END

EXEC sp_executesql @sqlStr, N'@CountParam int OUTPUT', @Count OUTPUT

if ( not (@CurrentPage <> 0 and CEILING(@Count / @PageSize) >= @CurrentPage))
set @CurrentPage = (case when @Count > 0 then 1 else 0 end)

SET @PageCount = (case when @Count > @PageSize then CEILING(@Count / @PageSize) else
(case when @Count > 0 then 1 else 0 end) end)

SET @rowStartIdx = 1 + (@PageSize * (CASE WHEN @CurrentPage = 0 THEN 0 ELSE @CurrentPage - 1 END))

SET @sqlStr = N'WITH Entries AS
(SELECT *'
+ CASE WHEN @SortOrder IS NOT NULL THEN ', ROW_NUMBER() OVER (ORDER BY ' + @SortOrder + ') AS RowNum'
 ELSE 'ID'
 END
+ ' FROM '+ @ObjectName
+ CASE WHEN @FilterText IS NOT NULL THEN ' WHERE '+ @FilterText ELSE '' END
+ ')
SELECT * FROM Entries WHERE (RowNum BETWEEN ' + CAST(@rowStartIdx AS nvarchar) + ' AND ' +
CAST(@rowStartIdx - 1 + ISNULL(@PageSize,0) AS nvarchar) + ')'

EXEC sp_executesql @sqlStr
END
index

create udl file to test sql connection to the sql server

Error: 18452 SQL Server 2005 "Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection."

How to copy a table with data to one db to another in sql 2005

How to insert multiple records to a table using a single Insert query

How to insert values to multiple tables using a single insert query statement

How to replace a part of a string of a field using query in MSSQL

ms sql Add field to table using Alter Table

ms sql server 2005 express edition

MSSQL What is the use of COALESCE() function

System.Data.SqlClient.SqlError The backup set holds a backup of a database other than the existing

What are the different login modes in sql server

where is sql server 2005 files stored

Windows 7 SQL Server 2008 Express R2 Error Unable to connect from remote machine desktop

sql server error exception system.data.sqlclient.sqlerror the media set has 2 media families but only 1 are provided

Restore a database to another database The backup set holds a backup of a database other than the existing

Stored Procedure Introduction

How to execute a stored procedure on the sql server query analzer

Create Stored Procedure Example For beginner

stored procedure variable orderby

Stored Procedcure to Update Table

Stored Procedure Input Variables

not getting sql server properties on clicking udl file opening as text

Generic Stored Procedure To Return Get Data From Table With Paging

Single Stored Procedure Insert Update Delete

Stored Perocedure ROWCOUNT

Invalid use of a side effecting operator EXECUTE STRING within a function

SQL Query MAX Value column

MSSQL Select GroupBy

SQL Occurrence of character in a string

TSQL Functions

t sql string manipulation STRING BETWEEN TWO STRINGS

TSQL CHECK IF NULL

Generic Stored Procedure Insert Update Delete

Cannot call methods on nvarchar max



  |  HOME   |  BLOG   |  TWITTER   |  ARTICLES   |  8086  |  C++   |  VC++   |  ASP .NET   |  VB .NET   |  JAVA SCRIPT   |  MS SQL   |  PHP   |  MY   |  VIDEOS   |  DOWNLOADS   |  CONTACT ME   |  

Copyright 2009 @ Murugan Andezuthu Dharmaratnam