Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

MSSQLÊý¾Ý¿âǶÌ×´æ´¢¹ý³ÌµÄÊÂÎñ¿ØÖÆ

   ÔÚMSSQL¹æ·¶ÖУ¬Ö§³ÖÊÂÎñǶÌ×£¬²»¹ýÔÚǶÌ×µ÷ÓÃ×Ó´æ´¢¹ý³Ìʱ£¬Òª»Ø¹öÊÂÎñ£¬Ðè°Ñ@@error´«¸ø×îÍâ²ãÊÂÎñ¡£¾ÙÀýÈçÏ£º
drop table testtb
go
create table testtb
(
 id int,name varchar(2)
)
go
--×Ó¹ý³Ì
drop proc proc_insert_sub
go
create proc proc_insert_sub
@errResult int out--·µ»Ø´íÎóÖµ
as begin
 declare @errsum int
 declare @transname varchar(20)
 set @transname='subtran'
 set @errsum=0
 --ÕýÈ·Êý¾Ý
    insert testtb values(1,'00')
 set @errsum=@errsum+@@error
 --´íÎóÊý¾Ý
 insert testtb values(2,'222')
 set @errsum=@errsum+@@error
 --ÕýÈ·Êý¾Ý
 insert testtb values(1,'11')
 set @errsum=@errsum+@@error
 set @errResult=@errsum
end
go
--Ö÷¹ý³Ì
drop proc proc_insert
go
create proc proc_insert
as begin
declare @errsum int
declare @errsum1 int
set @errsum=0
set @errsum1=0
  begin tran
  exec proc_insert_sub @errsum1 out
  print @errsum1
  set @errsum=@errsum+@errsum1
   print @errsum
print @@trancount
  if @errsum=0
 commit tran
  else
 ROLLBACK TRANSACTION  
end
go
/*Ö´ÐÐ
exec proc_insert
select * from testtb
*/


Ïà¹ØÎĵµ£º

½²½âMSSQLÊý¾Ý¿âÖÐSQLËø»úÖÆºÍÊÂÎñ¸ôÀë¼¶±ð

Ëø»úÖÆ
NOLOCKºÍREADPASTµÄÇø±ð¡£
1. ¿ªÆôÒ»¸öÊÂÎñÖ´ÐвåÈëÊý¾ÝµÄ²Ù×÷¡£
BEGIN TRAN t
INSERT INTO Customer
SELECT 'a','a'
2. Ö´ÐÐÒ»Ìõ²éѯÓï¾ä¡£
SELECT * from Customer WITH (NOLOCK)
½á¹ûÖÐÏÔʾ"a"ºÍ"a"¡£µ±1ÖÐÊÂÎñ»Ø¹öºó£¬ÄÇôa½«³ÉΪÔàÊý¾Ý¡£(×¢:1ÖеÄÊÂÎñδÌá½») ¡£NOLOCK±íÃ÷ûÓжÔÊý¾Ý±íÌí¼Ó¹²Ï ......

mssql Öн«ºº×Öת»»ÎªÆ´ÒôÊ××Öĸ·½·¨


1.
--½«ºº×Öת»»ÎªÆ´ÒôÊ××Öĸ
CREATE   function   GetAllPY(@str   nvarchar(4000))
returns   nvarchar(4000)
--WITH   ENCRYPTION
as
begin
declare   @intLen int
declare   @strRet nvarchar(4000)
declare   @temp   nvarchar(100)
set   @intLen &nb ......

½²½âMSSQLÊý¾Ý¿âÖÐSQLËø»úÖÆºÍÊÂÎñ¸ôÀë¼¶±ð

Ëø»úÖÆ
NOLOCKºÍREADPASTµÄÇø±ð¡£
1. ¿ªÆôÒ»¸öÊÂÎñÖ´ÐвåÈëÊý¾ÝµÄ²Ù×÷¡£
BEGIN TRAN t
INSERT INTO Customer
SELECT 'a','a'
2. Ö´ÐÐÒ»Ìõ²éѯÓï¾ä¡£
SELECT * from Customer WITH (NOLOCK)
½á¹ûÖÐÏÔʾ"a"ºÍ"a"¡£µ±1ÖÐÊÂÎñ»Ø¹öºó£¬ÄÇôa½«³ÉΪÔàÊý¾Ý¡£(×¢:1ÖеÄÊÂÎñδÌá½») ¡£NOLOCK±íÃ÷ûÓжÔÊý¾Ý±íÌí¼Ó¹²Ï ......

mssql row_number() partition ʹÓ÷½·¨Àí½â

Sql2005ÖÐʹÓÃow_number() partition½øÐзÖ×éʵÑ飬
SQL£º
select * from stu
select id,row_number() over (partition by snm order by id) from stu
½á¹û£º
id      snm
----------------
111 111V
111 111W
222 222N
333 3123
444 3123
555 3123
666 3232
777 3232
--·Ö×éºóµÄ½á¹û
id &n ......

ÆôÓÃMSSQL·Ö²¼Ê½ÊÂÎñµÄ³£¼ûÎÊÌâ

ÈçºÎ´´½¨Á´½Ó·þÎñÆ÷
IF  EXISTS (SELECT srv.name from sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'Á´½Ó·þÎñÆ÷Ãû')
EXEC master.dbo.sp_dropserver @server=N'Á´½Ó·þÎñÆ÷Ãû'', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver
 @server = N'Á´½Ó·þÎñÆ÷Ãû'', @srvproduct= ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ