SQL SERVER 2005¹ý³Ì·ÖÒ³
AspNetPagerÊÇÒ»¸ö±È½ÏºÃµÄ·ÖÒ³¿Ø¼þ,¸Ã¿Ø¼þʹÓùý³Ì·Öҳʮ·Ö·½±ã,¹ý³ÌÈçÏÂ:
Create PROCEDURE [dbo].[pt_AspNetPager]
(
@tableName varchar(255), -- ±íÃû
@fieldName varchar(400)= '*', -- ×Ö¶ÎÃû
@strWhere varchar(255) = Null,
@fieldOrder varchar(100), --²»ÄÜΪ¿Õ
@startIndex int,
@endIndex int
)
AS
declare @strSQL varchar(4000)
begin
set @strSQL='WITH temp AS (SELECT ROW_NUMBER() OVER (ORDER BY '+@fieldOrder+') as row,'+@fieldName+' from '+@tableName
if @strWhere<>''
SELECT @strSQL=@strSQL+' where '+@strWhere
SELECT @strSQL=@strSQL+')'
SELECT @strSQL=@strSQL+' SELECT * from temp where row between '+cast(@startIndex as varchar(10))+' and '+cast(@endIndex as varchar(10))
print @strSQL
exec (@strSQL)
END
Ïà¹ØÎĵµ£º
SQL³£Ó÷ÖÒ³µÄ°ì·¨:
±íÖÐÖ÷¼ü±ØÐëΪ±êʶÁУ¬[ID] int IDENTITY (1,1)
1.·ÖÒ³·½°¸Ò»£º(ÀûÓÃNot InºÍSELECT TOP·ÖÒ³)
Óï¾äÐÎʽ£º
SELECT TOP Ò³¼Ç¼ÊýÁ¿ *
from ±íÃû
WHERE (ID NOT IN
(SELECT TOP (ÿҳÐÐÊý*(Ò³Êý-1)) ID
from ±íÃû
ORDER BY ID))
ORDER BY ID
//×Ô ......
ÓÉÓÚ´úÂë¹ýÓÚ¼òµ¥£¬Ö±½ÓÌù³ö£º
SqlDataSourceEnumerator SseInstance = SqlDataSourceEnumerator.Instance;
//ServerName:·þÎñÆ÷Ãû;
//InstanceName:ʵÀýÃû;
//IsClustered:ÊÇ·ñΪȺ¼¯·þÎñÆ÷µÄÒ»²¿·Ö;
//Version:°æ±¾,8.*ÊÇSQL 2000,9.*ÊÇSQL 2005
DataTable DtSqlInstance = SseInstance.GetDataSources();
×¢£º ......
/*
Ô±í£º
thid other
a 1
a 1
b 0
b 0
......
SQLÄ£ºý²éѯÓï¾äºÍEscapeתÒå×Ö·û(2008-09-22 14:26:01)
±êÇ©£ºfrom תÒå×Ö·û sql µ¥ÒýºÅ ͨÅä·û escape it
ͨÅä·ûÃèÊöʾÀý%°üº¬Áã¸ö»ò¸ü¶à×Ö·ûµÄÈÎÒâ×Ö·û´®¡£WHERE title LIKE '%computer%' ½«²éÕÒ´¦ÓÚÊéÃûÈÎÒâλÖõİüº¬µ¥´Ê computer µ ......
Èç¹û°´ÕÕpowerdesignerµÄĬÈÏÑ¡Ïî,Éú³ÉµÄsql²»ÄÜÔÚmysqlÖÐÕýÈ·ÔËÐÐ.]
¾¹ý±¾ÈËÑо¿·¢ÏÖÒª×öÈçÏÂÐÞ¸Ä:
1.aftercreateÑ¡Ïî¸ÄΪ
.foreach_item(OutReferences)
.ifnot (%hasFKIndex%)
/*==============================================================*/
/* Index: ......