存储过程执行插入数据无错，但查询不到插入的数据

[jouwei]

CREATE TABLE [dbo].[InventoryTransaction] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[ExtTransactionID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[WarehouseID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DateCreated] [datetime] NULL ,
	[Reason] [varchar] (99) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Notes] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Reference] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SourceDocument] [int] NULL ,
	[VendorID] [int] NULL ,
	[GLPostedYN] [bit] NULL ,
	[QtyPostedYN] [bit] NULL ,
	[GLPostDate] [datetime] NULL ,
	[QtyPostDate] [datetime] NULL ,
	[TotalAmount] [money] NULL ,
	[SYS COM ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastModDate] [datetime] NOT NULL 
) ON [PRIMARY]

CREATE TABLE [dbo].[InventoryTransactionDetail] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[InventoryTransactionID] [int] NOT NULL ,
	[ProductID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UniquenessId] [int] NULL ,
	[SYS COM ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[WarehouseID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UOM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SourceQuantity] [int] NULL ,
	[Quantity] [int] NULL ,
	[Cost] [money] NULL ,
	[Extension] [money] NULL ,
	[Price] [money] NULL ,
	[GLAccount] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Source] [int] NULL ,
	[Message] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LocationID] [int] NULL ,
	[LastModDate] [datetime] NOT NULL ,
	[QtyAllocatedAdjusted] [int] NULL ,
	[QtyOnPOAdjusted] [int] NULL 
) ON [PRIMARY]
--两个表设置了主键和外键
ALTER TABLE [dbo].[InventoryTransactionDetail] ADD 
	CONSTRAINT [FK_InventoryTransactionDetail_InventoryTransaction] FOREIGN KEY 
	(
		[InventoryTransactionID]
	) REFERENCES [dbo].[InventoryTransaction] (
		[ID]
	)
GO
--执行插入的存储过程
CREATE    PROCEDURE Inventory_InsertTransactionHeader
(
	@ExtTransactionID varchar(50),
	@WarehouseID varchar(50),
	@DateCreated datetime,
	@Reason varchar(99),
	@Notes varchar(100),
	@Description varchar(50),
	@Reference varchar(50),
	@SourceDocument int,
	@VendorID int,
	@Type varchar(50),
	@QtyPostedYN bit,
	@GLPostedYN bit,
	@DealerID int,
	@TransactionID int output

)
--with encryption
AS

declare @CompanyID varchar(50)

If @VendorID = 0
	select @VendorID = null

select @CompanyID = [SYS COM ID] 
from [SYS Company]
where DealerID = @DealerID

Begin Tran

insert into InventoryTransaction (ExtTransactionID,WarehouseID,DateCreated,Reason,Notes,Description,Reference,SourceDocument,VendorID,Type,[SYS COM ID],GLPostedYN,QtyPostedYN)
values(@ExtTransactionID,@WarehouseID,@DateCreated,@Reason,@Notes,@Description,@Reference,@SourceDocument,@VendorID,@Type,@CompanyID,@GLPostedYN,@QtyPostedYN)

If @@Error <> 0
Begin
	Rollback Tran
	return 1
End

Select @TransactionID = SCOPE_IDENTITY()


Commit Tran

return 0

在程序中调用Inventory_InsertTransactionHeader可以返回正确的ID，但这是到数据库里去看，并没有这个ID对应的数据。
但是，

 declare @P1 int
set @P1=1626
exec Inventory_InsertTransactionHeader 'INV-ADJ-228', 'MAIN', 'Oct 13 2008 12:00:00:000AM', NULL, NULL, NULL, NULL, 15031, 0, 'Adjustments', 0, 0, 73, @P1 output
select @P1

如这样手工执行，是可以找到数据的。
请大家帮忙分析一下，非常感谢！！

[mjjzg]

慢慢调试吧,帮你顶下

[水族杰纶]

@@IDENTITY和SCOPE_IDENTITY和IDENT_CURRENT的区别
1,@@IDENTITY是得到当前会话的所有范围的最后插入的IDENTITY值 
2,SCOPE_IDENTITY是得到当前会话的当前范围的最后插入的IDENTITY值 
3，IDENT_CURRENT是得到指定表的最后插入的IDENTITY值，与会话、范围无关。

因为在插入和得到IDENTITY值之间可能会有其它的事情发生，但是你只想得到我刚才插入的IDENTITTY值，只有使用SCOPE_IDENTITY函数才行。

以下是测试SQL 
（1)
USE pubs
DROP TABLE t6
DROP TABLE t7
GO
CREATE TABLE t6(id int IDENTITY)
CREATE TABLE t7(id int IDENTITY(100,1))
GO
CREATE TRIGGER t6ins ON t6 FOR INSERT 
AS
BEGIN
   INSERT t7 DEFAULT VALUES
END
GO
--end of trigger definition

SELECT   * FROM t6
--id is empty.

SELECT   * FROM t7
--id is empty.

--Do the following in Session 1
INSERT t6 DEFAULT VALUES
declare @i int
set @i = 0
 while @i < 20000
   begin
   set @i = @i + 1
   print @i
   end

SELECT @@IDENTITY as "@@IDENTITY0"
/*Returns the value 100, which was inserted by the trigger.*/

SELECT SCOPE_IDENTITY()   'SCOPE_IDENTITY()0'
/* Returns the value 1, which was inserted by the 
INSERT stmt 2 statements before this query.*/

SELECT IDENT_CURRENT('t7') 'IDENT_CURRENT(t7)0'
/* Returns value inserted into t7, i.e. in the trigger.*/

SELECT IDENT_CURRENT('t6') 'IDENT_CURRENT(t6)0'
/* Returns value inserted into t6, which was the INSERT statement 4 stmts before this query.*/

-- Do the following in Session 2
SELECT @@IDENTITY "@@IDENTITY1"
/* Returns NULL since there has been no INSERT action 
so far in this session.*/

SELECT SCOPE_IDENTITY() 'SCOPE_IDENTITY()1'
/* Returns NULL since there has been no INSERT action 
so far in this scope in this session.*/

SELECT IDENT_CURRENT('t7') 'IDENT_CURRENT(t7)1'
/* Returns the last value inserted into t7.*/


当在执行while @i < 20000
   begin
   set @i = @i + 1
   print @i
   end这个地方的时候，就可以用


INSERT t6 DEFAULT VALUES
INSERT t7 DEFAULT VALUES
select * from t6
select * from t7来进行操作数据库

等到全部执行完了，你就会发现只有SCOPE_IDENTITY列永远是1

[hyde100]

看看

[jouwei]

刚回来。谢谢大家的解答，看的出来大家主要认为可能是 SCOPE_IDENTITY()的调用存在问题。
所以我存储过程做了个小调整

alter    PROCEDURE Inventory_InsertTransactionHeader
(
	@ExtTransactionID varchar(50),
	@WarehouseID varchar(50),
	@DateCreated datetime,
	@Reason varchar(99),
	@Notes varchar(100),
	@Description varchar(50),
	@Reference varchar(50),
	@SourceDocument int,
	@VendorID int,
	@Type varchar(50),
	@QtyPostedYN bit,
	@GLPostedYN bit,
	@DealerID int,
	@TransactionID int output

)AS

declare @CompanyID varchar(50)

If @VendorID = 0 
	select @VendorID = null
select @CompanyID = [SYS COM ID] from [SYS Company] where DealerID = @DealerID

Begin Tran

insert into InventoryTransaction (ExtTransactionID,WarehouseID,DateCreated,Reason,Notes,Description,Reference,SourceDocument,VendorID,Type,[SYS COM ID],GLPostedYN,QtyPostedYN)
values(@ExtTransactionID,@WarehouseID,@DateCreated,@Reason,@Notes,@Description,@Reference,@SourceDocument,@VendorID,@Type,@CompanyID,@GLPostedYN,@QtyPostedYN)

If @@Error <> 0
Begin
	Rollback Tran
	return -1
End
Select @TransactionID = SCOPE_IDENTITY()
Commit Tran

declare @return int
select @return=count(*) from InventoryTransaction where [ID]=@TransactionID
return @return

然后手工执行：

declare @P1 int,@out int 
set @P1=1626 
exec @out=Inventory_InsertTransactionHeader 'INV-ADJ-228', 'MAIN', 'Oct 13 2008 12:00:00:000AM', NULL, NULL, NULL, NULL, 15031, 0, 'Adjustments', 0, 0, 73, @P1 output 
select @P1 as [out],@out as [return]--查看retrun返回的值

返回是正确的数据：
(1 row(s) affected)

out return
----------- -----------
1645 1

(1 row(s) affected)
到此为止都是正确的=====================
但是我的问题是：当在我的程序中调用本存储过程，也可以得到正确的output和return,那么数据应该写入数据库里了，对吧？ 可是随着程序的运行，我也没有跟踪到任何删除语句，但是数据库就是找不到刚刚插入的数据。而且，如果往表InsertTransactionHeader里插入其他数据，会发现其ID也自动增长了一个。

[Garnett_KG]

用Profiler跟踪一下完整的操作，可能是执行完存储过程后某个地方的事务又Rollback了。

[pt1314917]

数据插入成功后找不到？

[水族杰纶]

[Quote=引用 9 楼 pt1314917 的回复:]
数据插入成功后找不到？
[/Quote]
沒數據?還是說自增列有數據,其他字段沒數據?

[jouwei]

[Quote=引用 10 楼 wufeng4552 的回复:]
引用 9 楼 pt1314917 的回复:
数据插入成功后找不到？

沒數據?還是說自增列有數據,其他字段沒數據?
[/Quote]
都没有，是通过再次插入数据看的出来自增列有增加过。

正在检查后面可有Rollback。不过，我的存储过程里已经Commit Tran了，还可以在其他存储过程里Rollback吗？

[Garnett_KG]

要在外层的事务提交，存储过程内部的事务才提交.
EX:

BEGIN TRAN
declare @P1 int,@out int 
set @P1=1626 
exec @out=Inventory_InsertTransactionHeader 'INV-ADJ-228', 'MAIN', 'Oct 13 2008 12:00:00:000AM', NULL, NULL, NULL, NULL, 15031, 0, 'Adjustments', 0, 0, 73, @P1 output 
select @P1 as [out],@out as [return]--查看retrun返回的值

ROLLBACK TRAN   --

[zero8500]

慢慢调试吧

帮你顶下

[pt1314917]

--try:

CREATE    PROCEDURE Inventory_InsertTransactionHeader
(
    @ExtTransactionID varchar(50),
    @WarehouseID varchar(50),
    @DateCreated datetime,
    @Reason varchar(99),
    @Notes varchar(100),
    @Description varchar(50),
    @Reference varchar(50),
    @SourceDocument int,
    @VendorID int,
    @Type varchar(50),
    @QtyPostedYN bit,
    @GLPostedYN bit,
    @DealerID int,
    @TransactionID int output
)
AS
If @VendorID = 0
    select @VendorID = null

insert into InventoryTransaction (ExtTransactionID,WarehouseID,DateCreated,Reason,Notes,Description,Reference,SourceDocument,VendorID,Type,[SYS COM ID],GLPostedYN,QtyPostedYN)
select @ExtTransactionID,@WarehouseID,@DateCreated,@Reason,@Notes,@Description,@Reference,@SourceDocument,@VendorID,@Type,[SYS COM ID],@GLPostedYN,@QtyPostedYN from [SYS Company]
where DealerID = @DealerID

set @TransactionID = @@identity
go

[中国风]

declare @P1 int,@out int
set @P1=1626
exec @out=Inventory_InsertTransactionHeader 'INV-ADJ-228', 'MAIN', 'Oct 13 2008 12:00:00:000AM', NULL, NULL, NULL, NULL, 15031, 0, 'Adjustments', 0, 0, 73, @P1 output
select @P1,@out--查看retrun返回的值

[rucypli]

试试IDENTITY

SCOPE_IDENTITY 和 @@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是，SCOPE_IDENTITY 只返回插入到当前作用域中的值；@@IDENTITY 不受限于特定的作用域。