create proc test @id int, @name nvarchar(100) as begin begin tran update tb set name=@name where id=@id if @@error<>0 begin commit tran select @id end else begin rollback select -1 end end
用存储过程更新前获取05可用output--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null drop table #T Go Create table #T([Col1] int,[Col2] Datetime,[Col3] int) Insert #T select 1,'2008-06-30 17:30:47.300',701 union all select 2,'2008-06-30 17:32:05.520',698 union all select 3,'2008-06-30 17:32:07.707',700 union all select 4,'2008-06-30 17:32:22.860',699 union all select 5,'2008-06-30 17:32:24.720',699 union all select 8,'2008-06-30 17:32:30.190',702 union all select 10,'2008-06-30 17:33:00.580',705 Go declare @T table(ID int)update #T set [Col1]=100 output inserted.[Col1] into @T where [Col1]=10select * from @T(1 行受影响) ID ----------- 100(1 行受影响)
谢谢您的回复 但是题的前提是只用一条SQL语句实现?
INSERT INTO jobs (job_desc,min_lvl,max_lvl)VALUES ('Accountant',12,125)SELECT @@identity AS 'identity'插入数据后SELECT @@identity AS 'identity'
用output吧,去看一下output的用法
如果符合逻辑的,如下:/*如果把数据插入变量表,可以这样:*/ declare @tb table(ID int,LastUpdateDate datetime) update tb set col='你更新的值' output inserted.[id],inserted.[LastUpdateDate] into @tb output inserted.[id],inserted.[LastUpdateDate] where condition='你的条件' /*如果不把数据插入变量表,可以这样:*/ update tb set col='你更新的值' output inserted.[id],inserted.[LastUpdateDate] where condition='你的条件'
如果不想的话,估计需要设置一个时间列和修改标志了,
@id int,
@name nvarchar(100)
as
begin
begin tran
update tb set name=@name where id=@id if @@error<>0
begin
commit tran
select @id
end
else
begin
rollback
select -1
end
end
用存储过程更新前获取05可用output--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([Col1] int,[Col2] Datetime,[Col3] int)
Insert #T
select 1,'2008-06-30 17:30:47.300',701 union all
select 2,'2008-06-30 17:32:05.520',698 union all
select 3,'2008-06-30 17:32:07.707',700 union all
select 4,'2008-06-30 17:32:22.860',699 union all
select 5,'2008-06-30 17:32:24.720',699 union all
select 8,'2008-06-30 17:32:30.190',702 union all
select 10,'2008-06-30 17:33:00.580',705 Go
declare @T table(ID int)update #T
set [Col1]=100
output inserted.[Col1]
into @T
where [Col1]=10select * from @T(1 行受影响)
ID
-----------
100(1 行受影响)
INSERT INTO jobs (job_desc,min_lvl,max_lvl)VALUES ('Accountant',12,125)SELECT @@identity AS 'identity'插入数据后SELECT @@identity AS 'identity'
用output吧,去看一下output的用法
如果符合逻辑的,如下:/*如果把数据插入变量表,可以这样:*/
declare @tb table(ID int,LastUpdateDate datetime)
update tb set col='你更新的值'
output inserted.[id],inserted.[LastUpdateDate]
into @tb
output inserted.[id],inserted.[LastUpdateDate]
where condition='你的条件'
/*如果不把数据插入变量表,可以这样:*/
update tb set col='你更新的值'
output inserted.[id],inserted.[LastUpdateDate]
where condition='你的条件'