sqlserver存储过程由浅入深

/* 存储过程名称： 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