SQLSERVER ·ÖÒ³´æ´¢¹ý³Ì(2 ÔÚSQL2005ÏÂʹÓÃ)
֮ǰÓÐÒ»¸öSQLServerµÄ·ÖÒ³´æ´¢¹ý³Ì µ«ÊÇÐÔÄܲ»ÊÇÊ®·ÖÀíÏë
ÓÖÕÒÁËÒ»¸ö
--SQL2005·ÖÒ³´æ´¢¹ý³Ì
/**
if exists(select * from sysobjects where name='fenye')
drop proc fenye
**/
CREATE procedure fenye
@tableName nvarchar(200) ,
@pageSize int,
@curPage int ,
@orderBy nvarchar(200) ,
@field nvarchar(200) = '*' ,
@condition nvarchar(200)
AS
SET NOCOUNT ON
DECLARE
@STMT nvarchar(max), -- SQL to execute
@recct int -- total # of records (for GridView paging interface)
IF LTRIM(RTRIM(@condition)) = '' SET @condition = '1 = 1'
IF @pageSize IS NULL BEGIN
SET @STMT = 'SELECT ' + @field + 'from ' + @tableName +'WHERE ' + @condition + 'ORDER BY ' + @orderBy
EXEC (@STMT) -- return requested records
END ELSE BEGIN
SET @STMT = 'SELECT @recct = COUNT(*) from ' + @tableName + ' WHERE ' + @condition
-- EXEC sp_executeSQL @STMT, @params = N'@recct INT OUTPUT', @recct = @recct OUTPUT
--SELECT @recct AS recct -- return the total # of records
DECLARE
@lbound int,
@ubound int
SET @curPage = ABS(@curPage)
SET @pageSize = ABS(@pageSize)
IF @curPage < 1 SET @curPage = 1
IF @pageSize < 1 SET @pageSize = 1
SET @lbound = ((@curPage - 1) * @pageSize)
SE
Ïà¹ØÎĵµ£º
ÓÉÓÚ¹¤×÷ÐèÇó£¬Òª¶Ô¸ºÔðµÄ²ú Æ·×öµãÐÔÄÜÓÅ»¯£¬ÔÚÍøÉÏÕÒµ½ÁËÏà¹ØµÄ¶«Î÷£¬Äà ³öÀ´Óë´ó¼Ò·ÖÏí£º
¿´µ½ºÜ¶àÅóÓѶÔÊý¾Ý¿âµÄÀí½â¡¢ÈÏʶ»¹ÊÇûÓÐÍ»ÆÆÒ»¸öÆ¿¾±£¬¶øÕâ¸öÆ¿¾±ÍùÍùÖ»ÊÇÒ»²ã´°Ö½£¬Ô½¹ýÁËÄ㽫¿´µ½Ò»¸öÐÂÊÀ½ç¡£
04¡¢05Äê×öÏîÄ¿µÄʱºò£¬ÓÃSQL Server 2000£¬ºËÐÄ±í£¨´ó²¿·ÖʹÓÃÆµ·±µÄ¹Ø¼ü¹¦ÄÜÿ´Î¶¼ÒªÓõ½£©´ïµ½ÁË800ÍòÊý¾Ý ......
ÎÒÔÚ°ÑoracleÊý¾Ýµ¼ÈësqlserverÖÐʱ£¬·¢ÏÖÔÚoracleÖÐ×ֶζ¨ÒåΪΨһË÷Òýʱ£¬²»Í¬¼Ç¼µÄ´Ë×Ö¶ÎÈç¹ûΪ¿Õ²»±»ÈÏΪÊÇÖØ¸´µÄ£¬µ«ÔÚsqlserverÖÐÈç¹û´Ë×Ö¶ÎΪΨһË÷Òý×ֶΣ¬²»ÔÊÐíÓÐ2¸öÒÔÉϵĿÕÖµ¡£ÓôÃÆ¡£ËùÒÔÖ»ºÃ½«sqlserverÖеÄΨһË÷Òý×Ö¶ÎÊÖ¹¤ÐÞ¸ÄΪ¼¸¸ö·Ç¿ÕµÄÖµ£¬µ«ÕâÑù³ÌÐò¿Ï¶¨Òª½øÐÐÐÞ¸ÄÁË¡£ÐèÒªÔÚ³ÌÐòÖÐΪ´Ë×Ö¶ÎÉèÖò»Öظ´ ......
create function comm_getpy
(
@str nvarchar(4000)
)
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--Èç¹û·Çºº×Ö×Ö·û£¬·µ»ØÔ×Ö·û
& ......
SQL ServerÊý¾Ý¿âÉè¼Æ±íºÍ×Ö¶Î
2009/02/18 12:29
1. Ôʼµ¥¾ÝÓëʵÌåÖ®¼äµÄ¹ØÏµ
¡¡¡¡¿ÉÒÔÊÇÒ»¶ÔÒ»¡¢Ò»¶Ô¶à¡¢¶à¶Ô¶àµÄ¹ØÏµ¡£ÔÚÒ»°ãÇé¿öÏ£¬ËüÃÇÊÇÒ»¶ÔÒ»µÄ¹ØÏµ£º¼´Ò»ÕÅÔʼµ¥¾Ý¶ÔÓ¦ÇÒÖ»¶ÔÓ¦Ò»¸öʵÌå¡£ÔÚÌØÊâÇé¿öÏ£¬ËüÃÇ¿ÉÄÜÊÇÒ»¶Ô¶à»ò¶à¶ÔÒ»µÄ¹ØÏµ£¬¼´Ò»ÕÅÔʼµ¥Ö¤¶ÔÓ¦¶à¸öʵÌ壬»ò¶àÕÅÔʼµ¥Ö¤¶ÔÓ¦Ò»¸öʵÌå¡£ÕâÀïµÄʵÌå¿ ......