ÎÊ sqlÓï¾ä£¬ ¸ø·Ö£¡ - MS-SQL Server / »ù´¡Àà

ÈçÏ£¡Çë¿´£¡

SQL code:

create procedure [dbo].[proc_TestPage]
@tablename nvarchar(255),--±íÃû
@columns nvarchar(255),--Òª²éѯµÄÁÐÃû
@sortcolumn nvarchar(255), --ÅÅÐò×Ö¶Î
@pagecount int, --ÿҳ¼Ç¼Êý
@pageindex int, --Ò³ºÅ
@wheres nvarchar(50),--Ìõ¼þ
@counts int output
as
begin
[color=#FF0000]select @counts=count(ID) from @tablename where @wheres[/color]
declare @beginrow int
declare @endrow int
set @beginrow=0
set @endrow=0
set @beginrow=(@pageindex-1)*@pagecount+1
set @endrow=@pageindex*@pagecount
declare @sqlstr nvarchar(500)
set @sqlstr='with table1 as(select '+ @columns +',ROW_NUMBER() OVER(ORDER BY '+ @sortcolumn+' ) AS ROW'+ ' from '+@tablename+')'
if @wheres !=''
begin
set @sqlstr=@sqlstr +' SELECT * from TABLE1 WHERE '+@wheres+' and ROW BETWEEN '+cast(@beginrow as nvarchar )+' and '+cast(@endrow as nvarchar)
end
else
begin
set @sqlstr=@sqlstr +' SELECT * from TABLE1 WHERE ROW BETWEEN '+cast(@beginrow as nvarchar )+' and '+cast(@endrow as nvarchar)
end
exec(@sqlstr)
end


ºìÉ«µÄÄǾ仰ִÐв»ÁË£¡
¾ÍÊÇ»ñÈ¡±íÖÐÓжàÉÙÌõÊý¾Ý£¡
Çó½â¾ö·½°¸£¡Ð»Ð»ÁË£¡·Ö¶ø²»ÊÇÎÊÌ⣡

---

SQL code:
¸Ä³É:
exec('select id fr