/*
存储过程名称: proc_Student_getAllStudentListOfPage
创建时间: 2016-03-15
创建人: 俞小晖
说明: 学生表-获取所有学生信息 (带参数分页且排序)
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_Student_getAllStudentListOfPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[proc_Student_getAllStudentListOfPage]
GO
CREATE PROCEDURE [dbo].[proc_Student_getAllStudentListOfPage]
@Search nvarchar(50),
@PageStrat nvarchar(50),
@PageEnd nvarchar(50),
@sortName nvarchar(50),
@sort nvarchar(50),
@TotalNum int out
AS
DECLARE @sqls nvarchar(3900)
DECLARE @sqlwhere nvarchar(500)
DECLARE @sqlsCount nvarchar(3900)
DECLARE @sqlsbranch nvarchar(3900)
SET @sqlwhere =''
IF @Search<>''
SET @sqlwhere = ' where student.stuName like ''%'+@Search+'%'' or student.major like''%'+@Search+'%'''
SET @sqls = 'select stuID,stuName,major from student'+@sqlwhere
print(@sqls)
SET @sqlsbranch=(' select * from ( select *,ROW_NUMBER()Over(order by '+@sortName+' '+@sort+') as RowId from( '+@sqls+' ) as PageTable )as PageTableT where RowId between '+@PageStrat+' and '+@PageEnd+' ')
SET @sqlsCount ='select @TotalNum=count(1) from ('+@sqls+') T'
EXEC SP_EXECUTESQL @sqlsCount,N'@TotalNum int output',@TotalNum output
EXEC SP_EXECUTESQL @sqlsbranch
GO
exec proc_Student_getAllStudentListOfPage @Search='',@PageStrat=1,@PageEnd=2,@sortName='stuName',@sort='desc',@TotalNum=1
sqlserver存储过程由浅入深
需积分: 50 34 浏览量
2016-03-17
15:41:58
上传
评论 4
收藏 5KB RAR 举报
阅薇草堂
- 粉丝: 2
- 资源: 90