set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
-- string sort = grdInv.SortCondition;
-- string filter = grdInv.Filter;
-- Business.Invoice.GetList(filter, sort, grdInv.CurrentPageIndex + 1);
ALTER PROCEDURE dbo.usp_InvoiceGetList (
@FieldQuery VARCHAR(8000) = '',
@Filters VARCHAR(1000) = '',
@Sort varchar(200) = '',
@PageNumber INT = 1,
@PageSize INT = 100
) AS
SET NOCOUNT ON SET XACT_ABORT ON SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @Tables VARCHAR(1000) --SET @Tables = 'Employee E LEFT JOIN Role R ON R.RoleID = E.RoleID' --SET @FieldQuery = 'EmployeeID, FirstName, LastName, Address1, City, State, Email, Phone, RoleName, R.RoleID'
EXEC usp_Paging_RowCount 'Invoice', 'InvoiceID', @Sort, @PageNumber, @PageSize, @FieldQuery, @Filters, NULL
ALTER PROCEDURE dbo.usp_Paging_RowCount (
@Tables varchar(8000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 100,
@Fields varchar(1000) = '',
@Filter varchar(5000) = NULL,
@Group varchar(1000) = NULL
) AS SET NOCOUNT ON SET XACT_ABORT ON
/Find the @PK type/ DECLARE @SortTable varchar(100) DECLARE @SortName varchar(100) DECLARE @strSortColumn varchar(200) DECLARE @operator char(2) DECLARE @type varchar(100) DECLARE @prec int
/Default Sorting/ IF @Sort IS NULL OR @Sort = ''
SET @Sort = @PK
IF (@Fields = '' OR @Fields IS NULL) SET @Fields = @PK IF (CHARINDEX(@PK, @Fields)SET @Fields = @PK + ',' + @Fields
-----duplicate columns error fixing
IF (CHARINDEX('',@Fields)>0) SET @Fields = '' ELSE BEGIN
-- set fields format as col1,col2,col3,... (there is no space between any two columns)
WHILE CHARINDEX(', ',@Fields)>0
BEGIN
SET @Fields = REPLACE(@Fields,', ',',')
END
DECLARE @Head VARCHAR(1000)
DECLARE @col VARCHAR(1000)
DECLARE @Tail VARCHAR(1000)
DECLARE @Anchor INT
SET @Head = ''
SET @col = ''
SET @Anchor = 0
SET @Tail = @Fields+','
WHILE LEN(@Tail) > 2
BEGIN
SET @Anchor = CHARINDEX(',',@Tail)
SET @col = SUBSTRING(@Tail,1,@Anchor)
SET @Tail = SUBSTRING(@Tail,@Anchor+1,LEN(@Tail)-@Anchor)
IF CHARINDEX(','+@col,','+@Tail)=0 SET @Head = @Head+@col
END
SET @Fields = SUBSTRING(@Head,1,LEN(@Head)-1)
END --print @Fields
DECLARE @strPageSize varchar(50) DECLARE @strStartRow varchar(50) DECLARE @strEndRow varchar(50) DECLARE @strFilter varchar(5000) DECLARE @strGroup varchar(1000)
/Default Page Number/ IF @PageNumber < 1
SET @PageNumber = 1
/Set paging variables./ --SELECT @PageSize = CAST(ParamValue AS INT) FROM Param WHERE ParamName='PageSize'
SET @strPageSize = CAST(@PageSize AS varchar(50)) SET @strStartRow = CAST(((@PageNumber - 1)@PageSize + 1) AS varchar(50)) SET @strEndRow = CAST((@PageNumber@PageSize) AS varchar(50)) /Set filter & group variables./ IF @Filter IS NOT NULL AND @Filter != ''
SET @strFilter = ' WHERE ' + @Filter + ' '
ELSE SET @strFilter = ''
IF @Group IS NOT NULL AND @Group != '' SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE SET @strGroup = ''
print 'WITH Ordered AS ( SELECT ROW_NUMBER() OVER (ORDER BY '+@Sort+') AS RowNumber, '+@Fields+' FROM '+@Tables+' '+@strFilter+' '+@strGroup+')
SELECT
FROM Ordered WHERE RowNumber between '+@strStartRow+' and '+@strEndRow
EXEC( 'WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY '+@Sort+') AS RowNumber, '+@Fields+' FROM '+@Tables+' '+@strFilter+' '+@strGroup+')
SELECT
FROM Ordered WHERE RowNumber between '+@strStartRow+' and '+@strEndRow )
Exec usp_Paging_RowCount_Count @Tables, @PK, @Filter, @PageSize
