SQL¡¡Êý¾Ý¿âÉú³É×Ô¶¯±àºÅ´úÂë
--exec [P_AutoGenerateNumber] 'reception_apply','generate_code','',7
/*
¹ý³Ì˵Ã÷:Éú³É×Ô¶¯±àºÅ
´´½¨Ê±¼ä:2010Äê1ÔÂ12ÈÕ
×÷Õß:feng
debug:ÉÐδ¿¼ÂDZàºÅÒç³öÇé¿ö
*/
ALTER proc [P_AutoGenerateNumber]
(
@tableName varchar(100)='meeting_apply', --±íÃû
@byteName varchar(100)='generate_code', --×Ö¶ÎÃû
@Remark varchar(10)='M', --±êʾ
@subLen int=4 --½ØÈ¡³¤¶È
)
WITH ENCRYPTION
as
declare @SQLString NVARCHAR(4000)
declare @OutputValue int
declare @subLenVar NVARCHAR(20)
declare @TempValue varchar(20)
set @subLenVar =convert(varchar(20),@subLen)
set @TempValue ='00000000000000000000'
declare @OutputValueToVar varchar(20)
set @SQLString = N'select @OutputValueEx=count(*) from '+@tableName+' where generate_code is not null or generate_code!='''''
EXECUTE sp_executesql @SQLString, N'@OutputValueEx int output',@OutputValueEx= @OutputValue output
if @OutputValue !=0
begin
SET @SQLString = N'SELECT @OutputValueEx=max(cast(right('+@byteName+','+@subLenVar+') as int))+1  
Ïà¹ØÎĵµ£º
ÈÕÖ¾ÐòÁбàºÅ(LSN)ÊÇÊÂÎñÈÕÖ¾ÀïÃæÃ¿Ìõ¼Ç¼µÄ±àºÅ¡£
µ±ÄãÖ´ÐÐÒ»´Î±¸·Ýʱ£¬Ò»Ð©LSNÖµ¾Í±»Í¬Ê±´æ´¢ÔÚÎļþ±¾Éí¼°msdb..backupset±íÖС£Äã¿ÉÒÔʹÓÃRESTORE HEADERONLYÓï·¨À´´Ó±¸·ÝÎļþÖлñÈ¡LSNÖµ¡£
×¢Ò⣺ÔÚSQL Server 2000ÖУ¬ÓÐÒ»ÁнÐ×öDifferentialBaseLSN¡£µ«ÔÚSQL Server 2005ÖУ¬ÏàͬµÄÁÐÃû³Æ±ä³ÉÁËData ......
£¨1£©ÕûºÏ¼òµ¥,ÎÞ¹ØÁªµÄÊý¾Ý¿â·ÃÎÊ£º
¡¡¡¡Èç¹ûÄãÓм¸¸ö¼òµ¥µÄÊý¾Ý¿â²éѯÓï¾ä,Äã¿ÉÒÔ°ÑËüÃÇÕûºÏµ½Ò»¸ö²éѯÖÐ(¼´Ê¹ËüÃÇÖ®¼äûÓйØÏµ)
£¨2£©É¾³ýÖØ¸´¼Ç¼£º
¡¡¡¡×î¸ßЧµÄɾ³ýÖØ¸´¼Ç¼·½·¨ ( ÒòΪʹÓÃÁËROWID)Àý×Ó£º
¡¡¡¡DELETE from EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
¡¡¡¡from EMP X WHERE X.EMP_NO = E.EM ......
ת×Ô£ºhttp://book.csdn.net/bookfiles/235/10023510864.shtml
ÔÚʵ¼ÊSQLÓ¦ÓÃÖУ¬¾³£ÐèÒª½øÐзÖ×é¾ÛºÏ£¬¼´½«²éѯ¶ÔÏó°´Ò»¶¨Ìõ¼þ·Ö×飬Ȼºó¶Ôÿһ¸ö×é½øÐоۺϷÖÎö¡£
GROUP BY×Ӿ䴴½¨·Ö×é
´´½¨·Ö×éÊÇͨ¹ýGROUP BY×Ó¾äʵÏֵġ£ÓëWHERE×Ӿ䲻ͬ£¬GROUP BY×Ó¾äÓÃÓÚ¹éÄÉÐÅÏ¢ÀàÐÍ£¬ÒÔ»ã×ÜÏà¹ØÊý¾Ý¡£¶øÎªÊ²Ã´ÒªÊ¹ÓÃGROUP BY ......
н¨±í£º
create table [±íÃû]
(
[×Ô¶¯±àºÅ×Ö¶Î] int IDENTITY (1,1) PRIMARY KEY ,
[×Ö¶Î1] nVarChar(50) default 'ĬÈÏÖµ' null ,
[×Ö¶Î2] ntext null ,
[×Ö¶Î3] datetime,
[×Ö¶Î4] money null ,
[×Ö¶Î5] int default 0,
[×Ö¶Î6] Decimal (12,4) default 0,
[×Ö¶Î7] image null ,
)
ɾ³ý±í£º
Drop table [±í ......
SQL ServerµÄ¸´ºÏË÷Òýѧϰ
¸ÅÒª
ʲôÊǵ¥Ò»Ë÷Òý,ʲôÓÖÊǸ´ºÏË÷ÒýÄØ? ºÎʱн¨¸´ºÏË÷Òý£¬¸´ºÏË÷ÒýÓÖÐèҪעÒâÐ©Ê²Ã´ÄØ£¿±¾ÆªÎÄÕÂÖ÷ÒªÊǶÔÍøÉÏһЩÌÖÂÛµÄ×ܽᡣ
Ò».¸ÅÄî
µ¥Ò»Ë÷ÒýÊÇÖ¸Ë÷ÒýÁÐΪһÁеÄÇé¿ö,¼´Ð½¨Ë÷ÒýµÄÓï¾äֻʵʩÔÚÒ»ÁÐÉÏ¡£
Óû§¿ÉÒÔÔÚ¶à¸öÁÐÉϽ¨Á¢Ë÷Òý£¬ÕâÖÖË÷Òý½Ð×ö¸´ºÏË÷Òý(×éºÏË÷Òý)¡£¸´ºÏË÷ÒýµÄ´´½¨· ......