通用翻页.sql

USE AdventureWorks;
GO
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60;

select * from (
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader)  as d
where rownumber between 11 and 20

create proc up_showPage1
(
 @pageSize int,
 @pageIndex int
)
as
begin

select * from (
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader)  as d
where rownumber between @pageSize*(@pageIndex-1)+1 and @pageSize*@pageindex
end

up_showPage1 5,1

select top 5 * from sales.salesOrderHeader

/************************************************************
存储过程: up_commonTurnPage
功能描述: 通用翻页
输入参数:
 @tableName: 表名或视图名称,必须
 @fieldList: 字段列表,默认值为 *
 @orderRule: 排序规则,必须,如 order by orderDate desc
 @pageSize: 每页行数,如 10
 @pageIndex: 当前页码,如 1
 @queryWhere:查询每件,不能加where,如 where name like '%sun%'
返 回 值: 无
测试范例:exec up_commonshowpage 'authors','au_lname,phone,address',
  'order by au_id',5,2,'au_lname like ''%s%'''
************************************************************/

CREATE PROCEDURE [dbo].[up_commonShowPage]
@tableName   varchar(255),  -- 表名或视图表
@fieldList varchar(1000) = '*', -- 字段列表
@orderRule varchar(255)='',  -- 排序规则
@pageSize   int = 10,   -- 每页行数
@pageIndex int = 1,    -- 当前页码
@queryWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
begin
declare @strSQL   varchar(1000)
if @queryWhere !=''
set @queryWhere=' where '+@queryWhere
set @strSQL='
 SELECT * FROM
(SELECT ROW_NUMBER() OVER ('+@orderRule+' ) AS rowNumber,
'+@fieldList+' FROM ['+@tableName+']'+@queryWhere+') AS NewTable
WHERE rowNumber BETWEEN '+str((@pageIndex-1)*@pageSize+1)+' AND '+str(@pageIndex*@pageSize)
exec (@strSQL)
end

select * from
(SELECT ROW_NUMBER() OVER (order by id) AS rowNumber,id,name,sex,title,departmentname
from view_employee_department where  name like '%金%') as newtable

where rowNumber between 1 and 5

exec up_commonshowpage 'view_employee_department','id,name,sex,title,departmentname',
  'order by id',5,4,'name like ''%黄%'''

select * from view_employee_department where name like '%黄%'

alter PROCEDURE up_commonShowPage
@tableName   varchar(255),  -- 表名或视图表
@fieldList varchar(1000) = '*', -- 字段列表
@orderRule varchar(255)='',  -- 排序规则
@pageSize   int = 10,   -- 每页行数
@pageIndex int = 1,    -- 当前页码
@queryWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
begin
declare @strSQL   varchar(1000)
if @queryWhere !=''
set @queryWhere=' where '+@queryWhere

set @strSQL='
 SELECT * FROM
(SELECT ROW_NUMBER() OVER ('+@orderRule+' ) AS rowNumber,'+@fieldList+' FROM ['+@tableName+']'+@queryWhere+') AS NewTable
WHERE rowNumber BETWEEN '+str((@pageIndex-1)*@pageSize+1)+' AND '+str(@pageIndex*@pageSize)
exec (@strSQL)
end
-----------------------------------------------------------------------------------------
求满足条件行数的存储过程

create proc up_commonRecord

declare @s varchar(200)
declare @count int
set @s='select count(*) from employees'

execute(set @count=@s)
select @count

declare  @totalRecords int
exec up_commonshowpage 'employees','id,phone,name','order by id',5,2,'name like ''%%'''
select * from authors

select * from authors

CREATE VIEW [dbo].[view_Employee_Department]
AS
SELECT     dbo.Employees.id, dbo.Employees.name, dbo.Employees.sex, dbo.Employees.birthday, dbo.Employees.title, dbo.Employees.departmentId,
                      dbo.Employees.phone, dbo.Employees.photo, dbo.Employees.Description, dbo.Department.Name AS DepartmentName
FROM         dbo.Employees INNER JOIN
                      dbo.Department ON dbo.Employees.departmentId = dbo.Department.id

select * from view_employee_department

 truncate table department

CREATE TABLE Department(
 [id] [int] IDENTITY(1,1) NOT NULL primary key,   --编号
 [Name] [nvarchar](10)  NOT NULL, --名称
 [phone] [varchar](15)  NULL,  --电话
 [leader] [nvarchar](10)  NULL,  --负责人
 [Description] [nvarchar](100)  NULL, --职能

)

drop table Employees

create table Employees(
id int identity not null primary key,
name nvarchar(10) not null,   --姓名
sex nchar(1) not null check(sex in('男','女')),
birthday smalldatetime check(birthday <getdate()),
title nvarchar(10),--职务
departmentId int references Department(id),--部门编号
phone varchar(50),
photo varchar(60),
Description nvarchar(200)
)

insert into department values('一部','12345678','赵','一部')
insert into department values('二部','12345678','钱','二部')
insert into department values('三部','12345678','孙','三部')
insert into department values('四部','12345678','李','四部')
insert into department values('五部','12345678','周','五部')

insert into employees values('赵一','男','1985-1-1','销售员',1,'111111111','photo/boy.gif','')

insert into employees values('赵二','男','1985-1-1','销售员',1,'111111111','photo/boy.gif','')

insert into employees values('赵三','男','1985-1-1','销售员',1,'111111111','photo/boy.gif','')

insert into employees values('赵四','男','1985-1-1','销售员',1,'111111111','photo/boy.gif','')

insert into employees values('赵五','男','1985-1-1','销售员',1,'111111111','photo/boy.gif','')

/************************************************************
存储过程: up_insertEmployee
功能描述: 注册新员工
输入参数:
 @name:员工姓名
 @sex: 性别
 @birthday: 生日
 @title: 职务
 @departmentId: 部门编号
 @phone: 电话
 @photo: 像片
 @description: 描述

返 回 值: 无
************************************************************/

alter PROCEDURE up_insertEmployee
 @name varchar(10),
 @sex nchar(1),
 @birthday smalldatetime,
 @title nvarchar(10),
 @departmentId int,
 @phone varchar(50),
 @photo varchar(60),
 @description nvarchar(200)
AS
begin
insert into  employees values(@name,@sex,@birthday,@title,@departmentId,@phone,@photo,@description)
end

select * from employees

/*******************************
 根据员工编号到视图中查询员工
*******************************/
alter proc up_getEmployeeById
(
 @id int
)
as
begin
 select * from view_employee_department where id=@id
end

up_getEmployeeById 1