如果在插入失败后选择SCOPE-IDENTITY()会发生什么(SQL Server 2005)

如果没有插入标识,SCOPE_Identity()将返回null,您可以通过将SCOPE_IDIDENTITY()分配给变量,然后检查变量内容来检查您指定的条件。

Create Proc SomeInsertToFail(@ID int OUTPUT)
as
Begin
    Select @ID =  Scope_Identity()
End
Declare @SOMEID int
Exec SomeInsertToFail @SOMEID OUTPUT
Select @SOMEID  --This will yield null

declare @tb table (i int identity, ky varchar(100));

insert into @tb values('Success');

insert into @tb select ky from @tb where ky = 'Failure';

select SCOPE_IDENTITY();  -- returns 1
select * from @tb

source:对空白查询(即无插入)执行SELECT scope_identity()