存储过程学习

Declare @Record Int
Declare @Page Int
Exec GetAlbums 0,10,@Record,@Page,''
Select * From Album

Alter PROCEDURE GetAlbums   
     @PageIndex INT,--第几页   
     @PageSize  INT,--每页多少条   
     @RecordCount INT OUT,--总条数   
     @PageCount INT OUT,--总页数   
     @WhereStr NVARCHAR (500)=''--查询条件   
AS   
DECLARE @tsql NVARCHAR(2000)   
IF @WhereStr = ''   
 SET @WhereStr = ' 1=1'   
SET @tsql=N'select @RecordCount = count(*) from album where ' + @WhereStr   
 exec sp_executesql @tsql,N'@RecordCount int output',@RecordCount output   
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)    
DECLARE @SQLSTR NVARCHAR(1000)   
IF @PageIndex = 0 OR @PageCount <= 1   
   SET @SQLSTR =N'SELECT TOP '+STR( @PageSize )+   
'   Id,User_Id,Name,Depiction,Photo_Path,(Case IsThrough When 0 Then ''否'' Else ''通过'' End) as StrIsThrough,   
   (Case IsCommend When 0 Then ''否'' Else ''是'' End) as StrIsCommend,AddTime,ServerUrl   
    FROM Album  where ' + @WhereStr +'  ORDER BY id DESC'   
ELSE IF @PageIndex = @PageCount - 1    
   SET @SQLSTR =N' SELECT *   
   FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+   
'   Id,User_Id,Name,Depiction,Photo_Path,(Case IsThrough When 0 Then ''否'' Else ''通过'' End) as StrIsThrough,   
   (Case IsCommend When 0 Then ''否'' Else ''是'' End) as StrIsCommend,AddTime,ServerUrl   
    FROM Album  where ' + @WhereStr +'  ORDER BY Id ASC ) TempTable  ORDER BY id DESC'   
ELSE             
     SET @SQLSTR =N' SELECT TOP  '+STR( @PageSize )+' *   
 FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+   
'   Id,User_Id,Name,Depiction,Path,Photo_Path,(Case IsThrough When 0 Then ''否'' Else ''通过'' End) as StrIsThrough,   
   (Case IsCommend When 0 Then ''否'' Else ''是'' End) as StrIsCommend,AddTime,ServerUrl   
    FROM Album  where ' + @WhereStr +'  ORDER BY id ASC )  TempTable ORDER BY id DESC'   
EXEC (@SQLSTR)   
--print @SQLSTR    
--------------------------------------------------   
  Go