0
I Use This!
Activity Not Available

Project Summary

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

Tags

No tags have been added

In a Nutshell, ptit-ai...

 No code available to analyze

Open Hub computes statistics on FOSS projects by examining source code and commit history in source code management systems. This project has no code locations, and so Open Hub cannot perform this analysis

Is this project's source code hosted in a publicly available repository? Do you know the URL? If you do, click the button below and tell us so that Open Hub can generate statistics! It's fast and easy - try it and see!

Add a code location

MIT License
Permitted

Commercial Use

Modify

Distribute

Private Use

Sub-License

Forbidden

Hold Liable

Required

Include Copyright

Include License

These details are provided for information only. No information here is legal advice and should not be used as such.

All Licenses

This Project has No vulnerabilities Reported Against it

Did You Know...

  • ...
    Black Duck offers a free trial so you can discover if there are open source vulnerabilities in your code
  • ...
    you can subscribe to e-mail newsletters to receive update from the Open Hub blog
  • ...
    there are over 3,000 projects on the Open Hub with security vulnerabilities reported against them
  • ...
    check out hot projects on the Open Hub

 No code available to analyze

Open Hub computes statistics on FOSS projects by examining source code and commit history in source code management systems. This project has no code locations, and so Open Hub cannot perform this analysis

Is this project's source code hosted in a publicly available repository? Do you know the URL? If you do, click the button below and tell us so that Open Hub can generate statistics! It's fast and easy - try it and see!

Add a code location

Community Rating

Be the first to rate this project
Click to add your rating
   Spinner
Review this Project!
Sample ohloh analysis