DECLARE @keys nvarchar(200)

DECLARE @pageindex int

DECLARE @pagesize int

DECLARE @classid int

set @keys = ''

set @pageindex = 1;

set @pagesize = 20;

set @classid = 17;

with temptbl as (

    select ROW_NUMBER() OVER (ORDER BY B.iid desc)AS Row, A.className,B.*

    from dl_PicClass A inner join  dl_PicFile B on a.iid = b.classid

    where (@keys = '' or A.className like '%'+@keys+'%' or B.fName like '%'+@keys+'%'  )

    and (@classid<=0 or B.classid = @classid)

)

SELECT * FROM temptbl where Row between (@pageindex-1)*@pagesize+1 and (@pageindex-1)*@pagesize+@pagesize;

select COUNT(1)

    from dl_PicClass A inner join  dl_PicFile B on a.iid = b.classid

    where (@keys = '' or A.className like '%'+@keys+'%' or B.fName like '%'+@keys+'%'  )

    and (@classid<=0 or B.classid = @classid)