¸ßЧSQL·ÖÒ³´æ´¢¹ý³Ì

CREATE PROCEDURE fenye
@tblName varchar(255)='wdf1', -- ±íÃû
@strGetFields varchar(1000) = '*', -- ÐèÒª·µ»ØµÄÁÐ
@fldName varchar(255)='userid', -- ÅÅÐòµÄ×Ö¶ÎÃû
@PageSize int = 10, -- Ò³³ß´ç
@PageIndex int = 1, -- Ò³Âë
@doCount bit = 0, -- ·µ»Ø¼Ç¼×ÜÊý, ·Ç 0 ÖµÔò·µ»Ø
@OrderType bit = 0, -- ÉèÖÃÅÅÐòÀàÐÍ, ·Ç 0 ÖµÔò½µÐò
@strWhere varchar(1500) = '' -- ²éѯÌõ¼þ (×¢Òâ: ²»Òª¼Ó where)
AS
declare @strSQL varchar(5000) -- Ö÷Óï¾ä
declare @strTmp varchar(110) -- ÁÙʱ±äÁ¿
declare @strOrder varchar(400) -- ÅÅÐòÀàÐÍ
if @doCount != 0
begin
if @strWhere !=''
set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere
else
set @strSQL = "select count(*) as Total from [" + @tblName + "]"
end
--ÒÔÉÏ´úÂëµÄÒâ˼ÊÇÈç¹û@doCount´«µÝ¹ýÀ´µÄ²»ÊÇ0£¬¾ÍÖ´ÐÐ×ÜÊýͳ¼Æ¡£ÒÔϵÄËùÓдúÂ붼ÊÇ@doCountΪ0µÄÇé¿ö
else
begin
if @OrderType != 0
begin
set @strTmp = "<(select min"
set @strOrder = " order by [" + @fldName +"] desc"
--Èç¹û@OrderType²»ÊÇ0£¬¾ÍÖ´ÐнµÐò£¬Õâ¾äºÜÖØÒª£¡
end
else
begin
set @strTmp = ">(select max"
set @strOrder = " order by [" + @fldName +"] asc"
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from [" + @tblName + "] where " + @strWhere + " " + @strOrder
else
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["+ @tblName + "] "+ @strOrder
--Èç¹ûÊǵÚÒ»Ò³¾ÍÖ´ÐÐÒÔÉÏ´úÂ룬ÕâÑù»á¼Ó¿ìÖ´ÐÐËÙ¶È
end
else
begin
--ÒÔÏ´úÂ븳ÓèÁË@strSQLÒÔÕæÕýÖ´ÐеÄSQL´úÂë
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder
if @strWhere != ''
set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from ["
+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["
+ @fldName + "]) from (select top " + str((@PageIn