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
Ïà¹ØÎĵµ£º
1.Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)¡¡¡¡
¡¡¡¡ SQLSERVERµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬Òò´Ëfrom×Ó¾äÖÐдÔÚ×îºóµÄ±í£¨»ù´¡±ídriving table£©½«±»×îÏÈ´¦Àí£¬ÔÚfrom×Ó¾äÖаüº¬¶à¸ö±íµÄÇé¿öÏ£¬±ØÐëÑ¡Ôñ¼Ç¼ÌõÊý×îÉٵıí×÷Ϊ»ù´¡±í£¬µ±SQLSERVER´¦Àí¶à¸ö±íʱ£¬»áÔËÓÃÅÅÐò¼°ºÏ²¢µÄ·½Ê½Á ......
· ±¾ÎÄÌÖÂÛÁËÈçºÎͨ¹ýTransact-SQLÒÔ¼°ÏµÍ³º¯ÊýOPENDATASOURCEºÍOPENROWSETÔÚͬ¹¹ºÍÒì¹¹Êý¾Ý¿âÖ®¼ä½øÐÐÊý¾ÝµÄµ¼Èëµ¼³ö£¬²¢¸ø³öÁËÏêϸµÄÀý×ÓÒÔ¹©²Î¿¼¡£
1. ÔÚSQL ServerÊý¾Ý¿âÖ®¼ä½øÐÐÊý¾Ýµ¼Èëµ¼³ö
(1).ʹÓÃSELECT INTOµ¼³öÊý¾Ý
  ......
¡¡¡¡¶ÔÊý¾Ý¿â½øÐжà±í²Ù×÷£¬Èç¹û±íÓë±íÖ®¼ä´æÔÚÒÀÀµ£¬ÄÇôÏÔʽµÄʹÓÃÊÂÎñ£¬¿ÉÒÔ±£³Ö¶ÔÊý¾Ý¿â²Ù×÷µÄÔ×ÓÐÔ¡£ÓÃPython·ÃÎÊSqlServerÊý¾Ý¿â£¬ÎÒʹÓÃpymssql¿â¡£½ñÌìÔÚʹÓÃÕâ¸ö¿âµÄʱºò£¬·¢ÏÖÒ»¸öÎÊÌâ¡£
ÎÊÌâ´ó¸ÅÊÇÕâÑùµÄ£º
¡¡¡¡ÎÒÓÐÁ½ÕÅ±í£¬Ò»ÕÅÖ÷±í£¨ClassInfo£©£¬Ò»ÕÅ´Ó±í£¨Student£©£¬Student±íͨ¹ýÍâ¼üClassIDÓëClassI ......
1.Èç¹ûÏÈprepare ºóÌí¼Ó²ÎÊý£¬ÕâÑùÒ»²¿·ÖÊý¾ÝÀàÐÍ¿ÉÒÔ²»ÓÃÉèÖÃÆäsize´óС£¬ÀýÈçchar
2.Èç¹ûÏÈÌí¼Ó²ÎÊýÔÙprepare£¬¾Í±ØÐëÉèÖòÎÊýµÄÀàÐÍ£¬´óС£¬¾«¶È²ÅÄÜͨ¹ý£¬±ÈÈçchar,varchar,decimalÀàÐÍ£¬¶øint,floatÓй̶¨×Ö½ÚÀàÐ͵ÄÊý¾ÝÀàÐÍÔò¿É²»ÓÃÉèÖôóС¡£
3.¹ØÓÚSqlServerµÄtimestampÀàÐÍ£º¸ÃÀàÐÍΪSqlServerµÄʱ¼ä´ÁÀàÐÍ£¬´´½ ......