spGetPagedSQL

Parameters

Parameter	Parameter Type	Mode
@PKFieldName	nvarchar(250)	IN
@CKFields	nvarchar(250)	IN
@SelectClause	nvarchar(4000)	IN
@FromClause	nvarchar(4000)	IN
@WhereClause	nvarchar(4000)	IN
@OrderByClause	nvarchar(500)	IN
@ResultsPerPage	int	IN
@CurrentPage	int	INOUT
@RowCount	int	INOUT
@Distinct	bit	IN
@USEGUIDID	bit	IN
@SELECTALLIDS	bit	IN
@SELECTALL	bit	IN
Definition

Copy

CREATE procedure [dbo].[spGetPagedSQL]
(
    @PKFieldName    nvarchar(250),
    @CKFields        nvarchar(250),
    @SelectClause    nvarchar(4000),
    @FromClause        nvarchar(4000),
    @WhereClause    nvarchar(4000),
    @OrderByClause    nvarchar(500),
    @ResultsPerPage    int, 
    @CurrentPage    int output,
    @RowCount       int output,
    @Distinct       bit=0,
    @USEGUIDID        bit = 0,
    @SELECTALLIDS    bit = 0,
    @SELECTALL      bit = 0
)
as
begin

    declare @sqlString nvarchar(max);

    if @SELECTALL = 1
    begin
        set @sqlString = 'SELECT '

        if @Distinct = 1
        begin
            set @sqlString = @sqlString + 'DISTINCT '
        end

        if len(@PKFieldName) > 0 
            set @sqlString = @sqlString+@SelectClause + ', ' + @PKFieldName+' from '+@FromClause
        else
            set @sqlString = @sqlString+@SelectClause + ', ' + @CKFields+' from '+@FromClause        

        if Len(@WhereClause)>0 set @sqlString=@sqlString+' where '+@WhereClause

        if @Distinct = 0
        begin
            if Len(@OrderByClause)>0 set @sqlString=@sqlString+' order by '+@OrderByClause
        end   

        EXECUTE(@sqlString);
    end
    else
    begin

        declare @FirstIndex    int
        declare @LastIndex     int
        declare @NumPages    int

        create table #PagedSQLList(
                ListID    int identity (1, 1) not null,
                PKID    int,
                CKID    nvarchar(64)
            )

        if @USEGUIDID = 1
        begin
            alter table #PagedSQLList
                drop column  PKID

            alter table #PagedSQLList
                add PKID    uniqueidentifier
        end

        set @sqlString = 'SELECT '

        if @Distinct = 1
        begin
            set @sqlString = @sqlString + 'DISTINCT '
        end

        if len(@PKFieldName) > 0 
            set @sqlString = @sqlString+@PKFieldName+' from '+@FromClause
        else
            set @sqlString = @sqlString+@CKFields+' from '+@FromClause

        if Len(@WhereClause)>0 set @sqlString=@sqlString+' where '+@WhereClause

        if @Distinct = 0
        begin
            if Len(@OrderByClause)>0 set @sqlString=@sqlString+' order by '+@OrderByClause
        end

        if @SELECTALLIDS = 1
        begin
            EXECUTE(@sqlString);
        end
        else
        begin    

            if len(@PKFieldName) > 0     
            begin        
                EXECUTE ('insert #PagedSQLList(PKID) ' + @sqlString)
                set @RowCount=@@Rowcount    
                CREATE NONCLUSTERED INDEX [IX_TEMP] ON #PagedSQLList(ListID, PKID)

            end
            else
            begin        
                EXECUTE ('insert #PagedSQLList(CKID) ' + @sqlString)
                set @RowCount=@@Rowcount
            end

            if @ResultsPerPage<1 set @ResultsPerPage=1

            -- determine number of pages
            set @NumPages = @RowCount/@ResultsPerPage
            if ((@RowCount%@ResultsPerPage) > 0) set @NumPages = @NumPages + 1

            if (@CurrentPage = 0) set @CurrentPage=1
            else if (@CurrentPage <= -1) set @CurrentPage=@NumPages
            else if (@CurrentPage > @NumPages) set @CurrentPage=@NumPages

            set @FirstIndex=((@CurrentPage-1)*@ResultsPerPage)+1
            set @LastIndex=@FirstIndex+@ResultsPerPage-1

            set @sqlString = 'SELECT '

            if @Distinct = 1
            begin
                set @sqlString = @sqlString + 'DISTINCT '
            end

            declare @sqlStringFrom nvarchar(max)
            if len(@PKFieldName) > 0
            begin
                set @sqlString = @sqlString +@SelectClause+', P.PKID  FROM '        
                set @sqlStringFrom = @FromClause+' JOIN #PagedSQLList P ON P.PKID='+@PKFieldName+' WHERE P.ListID BETWEEN ' + CAST(@FirstIndex as char)+' AND '+CAST(@LastIndex as char)           
            end
            else
            begin
                set @sqlString = @sqlString +@SelectClause+', P.CKID  FROM '
                set @sqlStringFrom = @FromClause+' JOIN #PagedSQLList P ON P.CKID='+@CKFields+' WHERE P.ListID BETWEEN '+CAST(@FirstIndex as char)+' AND '+CAST(@LastIndex as char)
            end

            declare @sqlStringWhere nvarchar(max)
            set @sqlStringWhere = ''
            if Len(@WhereClause)>0 set @sqlStringWhere=' AND '+@WhereClause

            declare @sqlStringOrder nvarchar(max)
            set @sqlStringOrder = ''
            if Len(@OrderByClause)>0 set @sqlStringOrder=' ORDER BY '+@OrderByClause

            EXECUTE (@sqlString + @sqlStringFrom + @sqlStringWhere + @sqlStringOrder);
        end
    end        
END