SQL Servicer Review notes (1) Stored procedure distribution

First step: Create a table

1 Declare @countInt int2 Declare @age int3 Set @age = -4 Set @countInt=15  while(@countInt<10000)6 begin7 8 Insert  intoStudent[SName],[SAge],[saddress])Values('Peng'+ Convert(nvarchar( -),@countInt),9 @age,'Beijing, China')Ten  One Set @countInt=@countInt+1; A Set @age=@age+1; - if(@age>= -) - begin the     Set @age= -; - End -  - End +  - GO + Drop TableStudent A Select *  fromStudent

View Code

Step two: Create a stored procedure

ALTER PROC [dbo].[Superpage](    /*Incoming Parameters*/    @TableName nvarchar( -),    @TableField nvarchar( -),--not used    @OrderBy nvarchar( $),     @OrderByType int,     @TableID nvarchar( $),     @StrWhere nvarchar( -),--not used    @TaoltCount int,    @PageSize int,    @CurrPage int,    @returnVal intoutput) as    DECLARE @ProcStrSQLCOUNT nvarchar(4000)    DECLARE @ProcOrderBy nvarchar( $)    DECLARE @returnCount int    DECLARE @TranPageSuper nvarchar( -)IF(@OrderByType!=1)BEGIN    SET @ProcOrderBy='Order by'+@OrderBy+ 'DESC';ENDELSEBEGIN    SET @ProcOrderBy='Order by'+@OrderBy+ 'ASC';ENDSELECT @TranPageSuper='mytransaction'/*total number of bars*/SET @ProcStrSQLCOUNT = 'SELECT @returnCount =count (1) from'+@TableName;BEGIN TRAN @TranPageSuperExecutesp_executesql@ProcStrSQLCOUNTN'@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)/*pages*/SET @returnVal = @returnCountCOMMIT TRAN @TranPageSuper--ROLLBACK TRAN @TranPageSuper

Step Three: Execute

DECLARE @return_valueint, @returnValintSELECT @returnVal=0EXEC @return_value=[dbo]. [Superpage] @TableName= N'Student', @TableField= N'*', @OrderBy= N'SID', @OrderByType=1, @TableID= N'SID', @StrWhere=NULL, @TaoltCount=0, @PageSize= -, @CurrPage=1, @returnVal=@returnVal outputselect @returnVal asN'@returnVal'SELECT'Return Value'=@return_valueGO

View Code