Sqlserver paging

Sqlserver on a table of paging query, but also through the second chapter table information
For example, there is a table
create table Student(
sid int primary key identity(1,1) ,
sname varchar(15) not null
)
Second tables
create table Comment(
id int primary key identity(1,1) ,
sid int not null
)
I need to second tables comment paging query, but also through the first table query name
To change how to write SQL code

Started by Benedict at November 13, 2016 - 3:06 AM

In view of the first, and then use this stored procedure.
if OBJECT_ID('sp_paging_Custom2005','P') is not null drop proc sp_paging_Custom2005
go
Create proc sp_paging_Custom2005
@tbname varchar(128), -- * name table or view name
@PageCurrent int, - display page (default)
@PageSize int, - page displays the number of (default)
@FieldShow varchar(1024), -- need to query the field (the default query fields)
@FieldOrder varchar(1024), -- sorting
@WhereString varchar(1024), -- query conditions
@RecordCount int output - the total number of output documents
as
Begin
set nocount on
/*The definition of the auxiliary variable*/
declare @sql nvarchar(max), - used to perform dynamic statement
@StartPoint int, -- the start node
@EndPoint int - end point
----------------------------------------------------------------------------------
/*The first step: filter*/
Check the existence of the table:
if OBJECT_ID(@tbname,'U') is null and OBJECT_ID(@tbname,'V') is null
Begin
RaisError('Unable to find the table or view, please check the parameter information!',16,1)
Return
End
-- check whether there is any sort field
if ISNULL(@FieldOrder,'')=''
Begin
--RaisError('The lack of order parameters, please check the parameter information!',16,1)
--return
set @FieldOrder='getdate()'
End
If not specified number of pages, the default setting for the first page
if ISNULL(@PageCurrent,0)=0
set @PageCurrent=1
If not specified on a per page number, the default is
if ISNULL(@PageSize,0)<0
set @PageSize=10
If display column is not specified, is listed as*
if ISNULL(@FieldShow,'')=''
set @FieldShow='*'
--where complete information condition
set @WhereString=case when ISNULL(@WhereString,N'')=N''
then N''
else N'where 1=1 and '+@WhereString
end
--Order by completion
set @FieldOrder= N'Order by '+@FieldOrder
----------------------------------------------------------------------------------
/*The second step: the statistics of the total number of*/
set @sql=
N'select
@RecordCount=count(1)
from '
+@tbname+N' '
+@WhereString+N' '
exec sp_executesql @sql,N'@RecordCount int output',@RecordCount output
----------------------------------------------------------------------------------
/*The third step: calculate the start point, end point, splicing SQL dynamic statement is necessary to query information*/
select @StartPoint=(@PageCurrent-1)*@PageSize+1,
@EndPoint=@StartPoint+@PageSize-1,
Dynamic statement -- mosaic
@sql=
N'select '+@FieldShow+
N' from
(
select row_number() over('+@FieldOrder+N') as CT_RowID, '+@FieldShow+
N' from '+@tbname+
N' '+@WhereString+
N' '+
N')tb
where CT_RowID between '+ltrim(@StartPoint)+N' and '+ltrim(@EndPoint)
exec(@sql)
set nocount off
End

Posted by Milly at November 27, 2016 - 3:52 AM

In view of the first, and then use this stored procedure. 
if OBJECT_ID('sp_paging_Custom2005','P') is not null drop proc sp_paging_Custom2005
go
Create proc sp_paging_Custom2005
@tbname varchar(128),	 -- * name table or view name	
@PageCurrent int,	 - display page (default)
@PageSize int,	 - page displays the number of (default)
@FieldShow varchar(1024),	 -- need to query the field (the default query fields)
@FieldOrder varchar(1024),	 -- sorting
@WhereString varchar(1024),	 -- query conditions
@RecordCount int output - the total number of output documents
as
Begin
set nocount on
/*The definition of the auxiliary variable*/
declare @sql nvarchar(max),	 - used to perform dynamic statement
@StartPoint int,	 -- the start node
@EndPoint int - end point
----------------------------------------------------------------------------------
/*The first step: filter*/
Check the existence of the table:
if OBJECT_ID(@tbname,'U') is null and OBJECT_ID(@tbname,'V') is null
Begin
RaisError('Unable to find the table or view, please check the parameter information!',16,1)
Return
End
-- check whether there is any sort field
if ISNULL(@FieldOrder,'')='' 
Begin
--RaisError('The lack of order parameters, please check the parameter information!',16,1)
--return
set @FieldOrder='getdate()'
End
If not specified number of pages, the default setting for the first page
if ISNULL(@PageCurrent,0)=0
set @PageCurrent=1
If not specified on a per page number, the default is
if ISNULL(@PageSize,0)<0
set @PageSize=10
If display column is not specified, is listed as*
if ISNULL(@FieldShow,'')=''
set @FieldShow='*'
--where complete information condition
set @WhereString=case when ISNULL(@WhereString,N'')=N'' 
  then N''
  else N'where 1=1 and '+@WhereString
 end
--Order by completion
set @FieldOrder= N'Order by '+@FieldOrder
----------------------------------------------------------------------------------	
/*The second step: the statistics of the total number of*/
set @sql=
N'select 
@RecordCount=count(1) 
from '
+@tbname+N' '
+@WhereString+N' '
exec sp_executesql @sql,N'@RecordCount int output',@RecordCount output
----------------------------------------------------------------------------------	
/*The third step: calculate the start point, end point, splicing SQL dynamic statement is necessary to query information*/
select @StartPoint=(@PageCurrent-1)*@PageSize+1,
   @EndPoint=@StartPoint+@PageSize-1,
   Dynamic statement -- mosaic
   @sql=
   N'select '+@FieldShow+
   N' from 
   (
select row_number() over('+@FieldOrder+N') as CT_RowID, '+@FieldShow+ 
N' from '+@tbname+
N' '+@WhereString+
N' '+
N')tb 
where CT_RowID between '+ltrim(@StartPoint)+N' and '+ltrim(@EndPoint)
exec(@sql)	
set nocount off
End

Posted by Milly at December 03, 2016 - 4:15 AM

First the wanted data into relational query view. Access to the view

Posted by dream at December 16, 2016 - 4:42 AM

Refer to the stored procedure:

CREATE PROC proc_pageview
@tbname     sysname,               -- to paging display table name
@FieldKey   nvarchar(1000),      In locating a record the primary key field (unique key), can be more than one field comma separated
@PageCurrent int=1,               Page - to display
@PageSize   int=10,                - page size (number of records)
@FieldShow nvarchar(1000)='',      -- a comma separated list of fields to be displayed, if not specified, it displays all fields
@FieldOrder nvarchar(1000)='',      -- a comma separated list of the sort field, you can specify the DESC specified in the field behind/ASC
@Where    varchar(1000)='',     -- query conditions
@RecordCount int OUTPUT - the total number of pages
AS
SET NOCOUNT ON
To check whether the object effectively
IF OBJECT_ID(@tbname) IS NULL
BEGIN
    RAISERROR(N'Object "%s" does not exist',1,16,@tbname)
    RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
    AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
    AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
    RAISERROR(N'"%s"Is not a table, view or table valued function',1,16,@tbname)
    RETURN
END

- page field inspection
IF ISNULL(@FieldKey,N'')=''
BEGIN
    RAISERROR(N'Paging processing requires a primary key (or Wei Yijian)',1,16)
    RETURN
END

Other parameters examined and specification
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
    SET @FieldOrder=N''
ELSE
    SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
    SET @Where=N''
ELSE
    SET @Where=N'WHERE ('+@Where+N')'

If @PageCount is NULL, then calculate the total number of pages (so that the design can only in the first time to calculate the total number of pages, after the call, the total number of pages returned to the stored procedure, avoid the computation again the total number of pages, the treatment does not want to calculate the total number of pages, you can assign @PageCount value)
IF @RecordCount IS NULL
BEGIN
    DECLARE @sql nvarchar(4000)
    SET @sql=N'SELECT @RecordCount=COUNT(*)'
        +N' FROM '+@tbname
        +N' '+@Where
    EXEC sp_executesql @sql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT
END

The calculation of TOPN value -- paging display
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
    @TopN1=(@PageCurrent-1)*@PageSize

-- the first page display
IF @PageCurrent=1
    EXEC(N'SELECT TOP '+@TopN
        +N' '+@FieldShow
        +N' FROM '+@tbname
        +N' '+@Where
        +N' '+@FieldOrder)
ELSE
BEGIN
    Alias - treatment
    IF @FieldShow=N'*'
        SET @FieldShow=N'a.*'

    - generating primary key (unique key) processing conditions
    DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000),
        @s nvarchar(1000),@Field sysname
    SELECT @Where1=N'',@Where2=N'',@s=@FieldKey
    WHILE CHARINDEX(N',',@s)>0
        SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1),
            @s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
            @Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field,
            @Where2=@Where2+N' AND b.'+@Field+N' IS NULL',
            @Where=REPLACE(@Where,@Field,N'a.'+@Field),
            @FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field),
            @FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)
    SELECT @Where=REPLACE(@Where,@s,N'a.'+@s),
        @FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s),
        @FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s),
        @Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''),    
        @Where2=CASE
            WHEN @Where='' THEN N'WHERE ('
            ELSE @Where+N' AND ('
            END+N'b.'+@s+N' IS NULL'+@Where2+N')'
    Executing the query
    EXEC(N'SELECT TOP '+@TopN
        +N' '+@FieldShow
        +N' FROM '+@tbname
        +N' a LEFT JOIN(SELECT TOP '+@TopN1
        +N' '+@FieldKey
        +N' FROM '+@tbname
        +N' a '+@Where
        +N' '+@FieldOrder
        +N')b ON '+@Where1
        +N' '+@Where2
        +N' '+@FieldOrder)
END
GO



Of course, if you are already familiar with LINQ, LINQ page more convenient

Posted by dream at December 28, 2016 - 5:26 AM

There is no source code, SQL, is the kind of statement directly

Posted by Benedict at January 02, 2017 - 7:20 AM

There is no source code, SQL, is the kind of statement directly

Posted by Benedict at January 03, 2017 - 5:27 AM

This is the LINQ page, more simple, but to be passed to Filter.PageIndex, Filter.PageSize, and sort

public static List<DepartmentVDM> GetDepartmentListByFilter(SimpleFilter filter)
{
    using (var context = new GlacierICREntities())
    {
        List<Department> list = context.Department.ToList();

        return list.OrderBy(a=>a.Name).Skip(filter.StartIndex).Take(filter.PageSize).ToList();
    }
}

Posted by dream at January 12, 2017 - 6:21 AM

Parameter @tbname is the name of the table, or view name

Posted by dream at January 14, 2017 - 6:26 AM