MSSQL´æ´¢¹ý³Ì
mssqlµÄ´æ´¢¹ý³ÌÀïÔõô»ñµÃÒ»ÌõSQLÓï¾äµÄÖ´ÐÐ״̬Êdzɹ¦»¹ÊÇʧ°Ü£¿ÎÒÏëÔÚÒ»ÕűíÖÐÔö¼Ó¼Ç¼£¬Ê×ÏÈÒª¼ì²éÊÇ·ñÒѾ´æÔÚÏàͬ¼Ç¼£¬Èç¹û´æÔھ͸üУ¬·ñÔò¾Í²åÈë¡£
ÎÒ¾ÍÏëÏÈÊÔÊÔ²åÈë²Ù×÷£¬Ê§°ÜÁ˾ÍÈ¥¸üУ¬»òÕßÏÈÈ¥¸üÐÂʧ°ÜÁ˾ÍÈ¥²åÈë¡£ÎÒ¸ÃÔõô֪µÀµÚÒ»ÌõÓï¾äÊÇ·ñÖ´Ðгɹ¦ÄØ£¿
Ò»¸ö´æ´¢¹ý³Ì´«Èë¶à¸ö²ÎÊý£¬Ôõô֪µÀij¸ö²ÎÊýÊÇ·ñÓÐÖµ´«Èë°¡£¿
SQL code:
1¡¢@@ERROR
2¡¢isnull()
SQL code:
if exists(select 1 from tb where ) --ÅжÏÊÇ·ñ´æÔÚÄãÒª²éÕҵļǼ Èç¹û´æÔÚÔòupdate
update tb set col=''
else
insert tb values() --Èç¹û²»´æÔÚÔò²åÈë
SQL code:
CREATE PROC P(@ID INT,@NAME VARCHAR(10))
AS
IF NOT EXISTS(SELECT * from TB T WHERE ID=@ID AND NAME=@NAME)
INSERT TB (ID,NAME)SELECT @ID,@NAME
ELSE
UPDATE TB SET ID=@ID ,NAME=@NAME WHERE Ìõ¼þ
??
¡¢@@ERROR
ÓÃÕâ¸öÅжÏÊÇ·ñ³É¹¦
Ò²¿ÉÒÔÓÃ@@ROWCOUNTÀ´ÅжÏ
SQL code:
²»¹ý¾õµÃÄãÕâ¸öÓ¦¸ÃÊÇ´¥·¢Æ÷À´·½±ã
CREATE TRIGGER TRI ON TB
INSTEAD OF INSERT
AS
IF NOT EXISTS(SELECT * from TB T WHERE EXISTS(SELECT
Ïà¹ØÎÊ´ð£º
asp+mssql ¸ÃÈçºÎ·µ»ØÈçÏÂÐÅÏ¢ÄØ
±ÈÈçÒ»¸ö±íÀïÃæµÄÊý¾Ý
date info
2009-1-1 13:00:00 12345
2009-1-1 13:00:01 12345
2009-1-1 13:00:02 12345
......
±ÈÈç
Ò»¸öÊÇÓû§Áбí
UserID
1
2
3
4
Ò»¸öÊÇ´ÎÊýÁбí(ÿÌì·ÃÎÊÒ»´ÎNum+1)
UserID Day Num
1 2009-09-22 10
2 2009-09-22 20
4 2009-09-22 8
......
Çë¿´ÒÔÏÂÀý×Ó Ð»Ð»£¡
Àý×Ó
A(×Ö¶Î)
------------------------
a b c
ab c
f c d
½á¹û:
A(×Ö¶Î)
------------------------È ......
ĿǰÓбíAºÍ±íB
ÆäÖÐ×Ö¶ÎÏàͬ
±íAÖÐÖ÷¼üidΪ×ÔÔö³¤
²¢ÇÒΪ´óÊý¾ÝÁ¿´¢´æ
ÔõôÑùʹÓô洢¹ý³Ì¿ÉÒÔ½«±íAÖеÄÊý¾Ý±¸·Ýµ½±íBÖУ¬²¢ÇÒɾ³ý±íAÖб¸·Ý¹ýµÄÊý¾Ý
¸ßÊÖ¸øÒ»¸ö´æ´¢¹ýÊ££¬Ð»Ð»
·ÖÇø±í¿ÉÒÔ½«±íÖÐµÄ ......