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)