首页 > 代码库 > Sql Servicer 复习笔记（1） 存储过程分布

Sql Servicer 复习笔记（1） 存储过程分布

2024-07-09 18:16:19   225人阅读

第一步：创建表

 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
View Code

 

第二步：创建存储过程

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
View Code

 


联系
我们