The most basic processing method:
If there is a primary key in the table (can be recorded without repeated fields), you can use the following method, of course, X, Y is replaced with specific numbers, and the variables cannot be used:
SELECT TOP Y *FOM table Where master key NOT IN (SELECT TOP (X-1) * Y master key from table)
If there is no primary key in the table, you can use a temporary table, add the identification field. The x, y can use variables.
Select ID = Identity (int, 1, 1), * INTO #TB FROM table
Select * from #tb where id between (x-1) * y and x * y-1
-------------------------------------------------- -------------
- Improve the general process of implementing the stored procedure.
/ * - Paging program implemented with stored procedures
Display page X of the specified query results
This stored procedure uses a temporary table method for the case where there is no primary key.
If there is a primary key, this method is not recommended
Zou Jian 2003.09 - * /
/ * - Call example
Exec p_show 'SELECT * from xzkh_new .. Regional Information'
Exec p_show 'Select * from xzkh_new .. Regional Information', 5, 2, 'Area number, area name, help code', 'Area number'
- * /
if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_show]') And ObjectProperty (id, n'isprocedure ') = 1)
Drop Procedure [DBO]. [p_show]
Go
Create Proc P_show
@Querystr nvarchar (1000), - query statement, if the query table, use: SELECT * FROM table
@PageSize INT = 10, - Size per page (number of lines)
@PAGECURRENT INT = 1, - Page to display
@Fdshow nvarchar (1000) = '' ,- To display the list of fields, if the query result is identified by the identity field, you need to specify this value and do not include the identification field.
@Fdorder nvarchar (1000) = '- List of Sort Fields
AS
Declare @fdname nvarchar (50) - Identification column name
, @ Id1 varchar (20), @ id2 varchar (20) - start and end record number
SELECT @fdname = '[id _' cast (newid () as varchar (40)) ']'
, @ Id1 = cast (@PageSize * (@ pagecurrent-1) as varchar (20))
, @ Id2 = cast (@ Pagesize * @ pagecurrent-1 as varchar (20))
, @ Fdshow = case isnull (@fdshow, '') when '' Ten '*' else @fdshow end
, @ Fdorder = case isnull (@fdorder, '') when '' Ten 'Else' Order by ' @ fdorder endexec (' select ' @ fdname = Identity (int, 0, 1),' @ fdshow '
INTO #TB from FROM (' @ querystr ') a ' @ fdorder '
SELECT ' @ fdshow ' from #tb where ' @ fdname ' Between '
@ Id1 'and' @ id2
)
Go
