返回用SQL存储过程插入的行的标识 [英] Return identity of row inserted with sql stored procedure

问题描述

我正在处理一个旧项目，需要插入一行并返回该行或它的身份.

I'm working on a legacy project and need to insert a row and return either that row or it's identity.

将使用正确的值插入新行，返回到.net时，数据集中将不会返回任何内容.

the new row is inserted with the correct values, only nothing is returned in the dataset when it gets back to .net.

我尝试选择 @@ identity ， RETURN ， OUTPUT ，但我尝试使用所有方法，数据集为空(但不为null)).

I've tried selecting @@identity, RETURN, OUTPUT, but with everything i try the dataset is empty (but not null).

这不是 MyUtils.DBHelper.GetDataSet 的错，因为它在其他地方使用并执行并返回确定.

It's not the fault of MyUtils.DBHelper.GetDataSet, as this is used in other places and executes and returns ok.

USE [dbname]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[DuplicateOrder]
(
    @sourceid int,
    @var1 int,
    @var2 decimal(10,2),
)
AS

INSERT INTO OrderHeader (
    Users_ID,
    Stores_ID,
    )
SELECT 
    @var1,
    @var2
FROM Order
WHERE id = @sourceid
GO

我用来执行存储过程的代码是:

The code i'm using to execute the stored procedure is:

Using cmd As New SqlCommand("DuplicateOrder") With {.CommandType = CommandType.StoredProcedure}
                    cmd.Parameters.AddWithValue("@sourceid", sourceId)
                    cmd.Parameters.AddWithValue("@var1 ", var1 )
                    cmd.Parameters.AddWithValue("@var2 ", var2 )
                    ds = MyUtils.DBHelper.GetDataSet(cmd)
End Using

推荐答案

这应该可以解决问题:

ALTER Procedure [dbo].[DuplicateOrder]
(
    @sourceid int,
    @var1 int,
    @var2 decimal(10,2)
)
AS

INSERT INTO OrderHeader (
    Users_ID,
    Stores_ID
    )
SELECT 
    @var1,
    @var2

SELECT @@IDENTITY AS ident
GO

我在SQL Server上对其进行了测试，它返回一行带有 ident 列的列.

I tested it on SQL server and it returns one row with the column ident.

这篇关于返回用SQL存储过程插入的行的标识的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！