分页存储过程.

ALTER  procedure [dbo].[CMS_GetRecord]  @PageIndex int = 1,  @PageSize int = 15,  @Where varchar(400) = '',  @OrderBy varchar(50) = 'record_id',  @OutPut int output as Begin  Declare @strSql varchar(500)

 DECLARE   @SQL   NVARCHAR(1000)  DECLARE   @R BIGINT

 SET   @SQL=  N'select @R=count(*) from  cms_record LEFT JOIN cms_record_content ON record_id=content_record_id AND content_lang_id=1 WHERE 1=1 '+@Where  EXEC  SP_EXECUTESQL   @SQL,  N' @R BIGINT OUTPUT',  @R OUTPUT  SET   @OutPut=  @R --set @OutPut = @@RowCount  if(@PageIndex =1)  Begin     set @strSql='select top '+str(@PageSize)+' record_id,record_order_num,record_section_id,record_display,record_display_from,record_display_to,record_pdf_filename,record_picture_filename,record_word_filename,record_related_links_category,record_url,record_sample_type,record_sample_category,record_status,content_title,content_detail from cms_record LEFT JOIN cms_record_content ON record_id=content_record_id AND content_lang_id=1 WHERE 1=1 '+@Where+' order by '+@OrderBy+' ASC'  End  Else  Begin     set @strSql='select top '+str(@PageSize)+' record_id,record_order_num,record_section_id,record_display,record_display_from,record_display_to,record_pdf_filename,record_picture_filename,record_word_filename,record_related_links_category,record_url,record_sample_type,record_sample_category,record_status,content_title,content_detail from cms_record LEFT JOIN cms_record_content ON record_id=content_record_id AND content_lang_id=1 where ( '+@OrderBy      +' < ( select Min( '+@OrderBy+') from ( select top '+str(@PageSize*(@PageIndex-1))+' * from  cms_record LEFT JOIN cms_record_content ON record_id=content_record_id AND content_lang_id=1 order by '      +@OrderBy+' ASC) as tempTable)) order by '+ @OrderBy+' ASC'  End

 exec(@strSql) End