hi jack,

this is my exact procedure.

CREATE procedure [dbo].[Search_and_paging]

@art_Filter varchar(8000),

@sect_Filter varchar(8000),

@PageNumber int=1,

@pageSize int=20

as

begin

declare @strSQL nvarchar(4000), @art varchar(8000), @sect varchar(8000), @orderBy varchar(30),

@FIELDS varchar(100), @startRowNo int , @endRowNo int

--field to be selected

set @FIELDS=' sid , title , url, pdt, intro, intro2 '

-- first table as article selection

set @art=' from ( select art_id as sid , art_title as title, ''/article.aspx?artid=''+art_encryptid as url ,

art_displaydt as pdt,

case art_index_intro when '''' then art_intro else isnull(art_index_intro, art_intro) end as intro,

substring(art_article,1,300) as intro2

from us_article

where art_status = 3 and art_article_type!=5 ' + isnull(@art_Filter,'')

-- second table as section selection

set @sect=' union

select sect_id as sid , sect_title as title, sect_page+sect_encryptid as url,

sect_publishdt as pdt,

case sect_index_intro when '''' then sect_intro else isnull(sect_index_intro, sect_intro) end as intro,

substring(sect_dtl,1,300) as intro2

from us_section

where sect_status = 3 and sect_page is not null and sect_page <> '''' ' + isnull(@sect_Filter,'') + ' )a '

-- for order by

set @orderBy= ' order by pdt desc '

-- forming query

set @startRowNo =((@PageNumber * @pageSize) - (@pageSize -1) )

set @endRowNo=(@PageNumber * @pageSize)

set @strSQL= N' WITH Search AS (select ROW_NUMBER() OVER ('+ @orderBy+') AS ROWID, '+ @FIELDS + @art + @sect +' ) select * FROM Search where ROWID between '+ convert(varchar(50),@startRowNo) + ' and ' +convert(varchar(50),@endRowNo)

EXECUTE SP_EXECUTESQL @strSQL

-- for total selected records

-- forming query

set @strSQL= N' select Count(sid) ' + @art + @sect

EXECUTE SP_EXECUTESQL @strSQL

end