首页 > 代码库 > Sql Servicer 复习笔记(1) 存储过程分布
Sql Servicer 复习笔记(1) 存储过程分布
第一步:创建表
1 declare @countInt int 2 declare @age int 3 set @age =20 4 set @countInt=1 5 while(@countInt<10000) 6 begin 7 8 insert into student([sName],[sAge],[sAddress])values(‘彭‘+ convert(nvarchar(30),@countInt), 9 @age,‘中国北京‘)10 11 set @countInt=@countInt+1;12 set @age=@age+1;13 if(@age>=50)14 begin15 set @age=25;16 end17 18 end19 20 GO21 drop table student22 select * from student
第二步:创建存储过程
ALTER PROC [dbo].[SuperPage]( /*传入参数*/ @TableName nvarchar(20), @TableField nvarchar(2000), --未用 @OrderBy nvarchar(200), @OrderByType int, @TableID nvarchar(200), @StrWhere nvarchar(2000), --未用 @TaoltCount int, @PageSize int, @CurrPage int, @returnVal int output)AS DECLARE @ProcStrSQLCOUNT nvarchar(4000) DECLARE @ProcOrderBy nvarchar(200) DECLARE @returnCount int DECLARE @TranPageSuper nvarchar(50)IF(@OrderByType!=1)BEGIN SET @ProcOrderBy=‘ Order By ‘+@OrderBy+ ‘ DESC ‘;ENDELSEBEGIN SET @ProcOrderBy=‘ Order By ‘+@OrderBy+ ‘ ASC ‘;ENDSELECT @TranPageSuper=‘MyTransaction‘/*总条数*/SET @ProcStrSQLCOUNT = ‘SELECT @returnCount=Count(1) FROM ‘+@TableName;BEGIN TRAN @TranPageSuperexecute sp_executesql @ProcStrSQLCOUNT,N‘@returnCount int out‘,@returnCount outSET @returnCount=(@returnCount-1)/@PageSize+1print @returnCountexec(‘SELECT TOP ‘+@PageSize+‘ *FROM ‘+@TableName+‘WHERE (‘+@TableID+‘ NOT IN (SELECT TOP (‘+@PageSize+‘*‘+@CurrPage+‘) ‘+@TableID+‘ FROM ‘ +@TableName + ‘ ‘+ @ProcOrderBy+‘)) ‘ +@ProcOrderBy)/*页数*/SET @returnVal = @returnCountCOMMIT TRAN @TranPageSuper--ROLLBACK TRAN @TranPageSuper
第三步:执行
DECLARE @return_value int, @returnVal intSELECT @returnVal = 0EXEC @return_value = [dbo].[SuperPage] @TableName = N‘Student‘, @TableField = N‘*‘, @OrderBy = N‘sID‘, @OrderByType = 1, @TableID = N‘sID‘, @StrWhere = NULL, @TaoltCount = 0, @PageSize = 20, @CurrPage = 1, @returnVal = @returnVal OUTPUTSELECT @returnVal as N‘@returnVal‘SELECT ‘Return Value‘ = @return_valueGO
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。