首页 > 代码库 > 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
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 = NStudent,        @TableField = N*,        @OrderBy = NsID,        @OrderByType = 1,        @TableID = NsID,        @StrWhere = NULL,        @TaoltCount = 0,        @PageSize = 20,        @CurrPage = 1,        @returnVal = @returnVal OUTPUTSELECT    @returnVal as N@returnValSELECT    Return Value = @return_valueGO
View Code