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