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 ';
ENDSELECT @TranPageSuper='MyTransaction'/*总条数*/
SET @ProcStrSQLCOUNT = 'SELECT @returnCount=Count(1) FROM '+@TableName;
BEGIN TRAN @TranPageSuper
execute sp_executesql @ProcStrSQLCOUNT,N'@returnCount int out',@returnCount outSET @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 intSELECT @returnVal = EXEC @return_value = [dbo].[SuperPage]
@TableName = N'Student',
@TableField = N'*',
@OrderBy = N'sID',
@OrderByType = ,
@TableID = N'sID',
@StrWhere = NULL,
@TaoltCount = ,
@PageSize = ,
@CurrPage = ,
@returnVal = @returnVal OUTPUTSELECT @returnVal as N'@returnVal'SELECT 'Return Value' = @return_valueGO