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

第一步：创建表

declare @countInt int
declare @age int
set @age =20
set @countInt=1
while(@countInt<10000)
begin

insert into student([sName],[sAge],[sAddress])values('彭'+ convert(nvarchar(30),@countInt),
@age,'中国北京')

set @countInt=@countInt+1;
set @age=@age+1;
if(@age>=50)
begin
    set @age=25;
end

end

GO
drop table student
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 ';
END
ELSE
BEGIN
    SET @ProcOrderBy=' Order By '+@OrderBy+ ' ASC ';
END

SELECT @TranPageSuper='MyTransaction'

/*总条数*/
SET @ProcStrSQLCOUNT = 'SELECT @returnCount=Count(1) FROM '+@TableName;
BEGIN TRAN @TranPageSuper
execute sp_executesql @ProcStrSQLCOUNT,N'@returnCount int out',@returnCount out

SET @returnCount=(@returnCount-1)/@PageSize+1
print @returnCount
exec('
SELECT TOP '+@PageSize+' *
FROM '+@TableName+'
WHERE ('+@TableID+' NOT IN
          (SELECT TOP ('+@PageSize+'*'+@CurrPage+') '+@TableID+'
         FROM ' +@TableName + ' '+ @ProcOrderBy+')) '
         +@ProcOrderBy)
/*页数*/
SET @returnVal = @returnCount
COMMIT TRAN @TranPageSuper

--ROLLBACK TRAN @TranPageSuper

第三步：执行

DECLARE    @return_value int,
        @returnVal int

SELECT    @returnVal = 0

EXEC    @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 OUTPUT

SELECT    @returnVal as N'@returnVal'

SELECT    'Return Value' = @return_value

GO