存储过程合并(插入或更新)和返回 ID 问题 [英] Stored procedure MERGE (INSERT OR UPDATE) and return ID issue
问题描述
所以我正在努力学习如何使用存储过程，并且在大多数情况下，我有点了解如何使用它们，除了这个合并过程.
So I'm trying to learn how to use stored procedures and for the most part I somewhat understand how to use them except for this merge procedure.
我想要做的是使用 MERGE 过程插入如果行不存在并返回 ID ELSE IF NOT EXISTS 然后只返回 ID.
What I'm trying to do is use the MERGE procedure to Insert if a row does not already exist and return the ID ELSE IF NOT EXISTS then just return the ID.
这是目前我所拥有的.
CREATE PROCEDURE dbo.authors_InsertOrUpdate
-- Add the parameters for the stored procedure here
@FirstName nvarchar(50),
@LastName nvarchar(50),
@id int = NULL OUTPUT
AS
MERGE Authors AS target
USING (SELECT @FirstName, @LastName) AS source (FirstName, LastName)
ON target.FirstName = source.FirstName AND target.LastName = source.LastName
WHEN MATCHED THEN
UPDATE
SET @id = SCOPE_IDENTITY()
WHEN NOT MATCHED THEN INSERT
(FirstName, LastName) VALUES (source.FirstName, source.LastName);
SET @id = SCOPE_IDENTITY()
只要将值/信息输入数据库并找到现有的，一切正常，但我没有正确取回我的 ID 值.这是我使用它的方法..
Everything works fine as far as inputting the values/information into the database and finding an existing one but I'm not getting my ID value back correctly. Here is my method that uses it..
private static int storeAuthors(string firstName, string lastName)
{
String commandText = "dbo.authors_InsertOrUpdate";
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AmazonCrawler.Properties.Settings.database"].ToString()))
{
using (SqlCommand command = new SqlCommand(commandText, connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@FirstName", firstName));
command.Parameters.Add(new SqlParameter("@LastName", lastName));
SqlParameter authorId = new SqlParameter("@id", SqlDbType.Int);
authorId.Direction = ParameterDirection.Output;
command.Parameters.Add(authorId);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
return Convert.ToInt32(authorId.Value);
}
}
}
请帮我解决这个问题！
提前致谢.
---------编辑------------
---------EDIT------------
已将程序更新为此，但当我尝试返回值时，我的代码中仍然收到空值
Updated the procedure to this yet I'm still receiving a null value in my code when i try to return the value
CREATE PROCEDURE dbo.authors_InsertOrUpdate
-- Add the parameters for the stored procedure here
@FirstName nvarchar(50),
@LastName nvarchar(50),
@id int OUTPUT
AS
MERGE Authors AS target
USING (SELECT @id, @FirstName, @LastName) AS source (id, FirstName, LastName)
ON target.FirstName = source.FirstName AND target.LastName = source.LastName
WHEN NOT MATCHED THEN
INSERT (FirstName, LastName) VALUES (source.FirstName, source.LastName);
SET @id = SCOPE_IDENTITY()
编辑参考 1
编辑参考 2
编辑参考文献 3
ALTER PROCEDURE dbo.authors_InsertOrUpdate
-- Add the parameters for the stored procedure here
@FirstName nvarchar(50),
@LastName nvarchar(50),
@id int OUTPUT
AS
MERGE Authors AS target
USING (SELECT @id, @FirstName, @LastName) AS source (ID, FirstName, LastName)
ON target.FirstName = source.FirstName AND target.LastName = source.LastName
WHEN MATCHED THEN
UPDATE SET @id = target.ID
WHEN NOT MATCHED THEN
INSERT (FirstName, LastName) VALUES (source.FirstName, source.LastName);
SET @id = SCOPE_IDENTITY()
SELECT @id
仍然收到参考文献 2 中列出的相同错误.=(还没有解决方案.
Still receiving same error listed in reference 2. =( no solution yet.
这是我传入的值，返回值为零作为 id
here is my passed in values with a return of zero as an id
这是我的数据库
编辑参考文献 4
在将值放入此值时尝试更改我的参数，因为我认为可能在获取值之前关闭它会导致问题但没有运气
Tried changing my parameters when putting in the values to this as I thought perhaps closing it before getting the value was causing the problem but no luck
private static int storeAuthors(string firstName, string lastName)
{
String commandText = "dbo.authors_InsertOrUpdate";
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AmazonCrawler.Properties.Settings.database"].ToString()))
{
using (SqlCommand command = new SqlCommand(commandText, connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@FirstName", firstName));
command.Parameters.Add(new SqlParameter("@LastName", lastName));
SqlParameter authorId = new SqlParameter("@id", SqlDbType.Int);
authorId.Direction = ParameterDirection.Output;
command.Parameters.Add(authorId);
connection.Open();
command.ExecuteNonQuery();
int value = Convert.ToInt32(authorId.Value);
connection.Close();
return value;
}
}
}
推荐答案
在 UPDATE 情况下，您没有生成新的 IDENTITY，您只是在查询，因此 SCOPE_IDENTITY() 不会返回正确的值.在 UPDATE 的情况下，SCOPE_IDENTITY() 起初可能看起来有效，但它实际上是从作用域中的最后一个 INSERT 返回值(我认为)，这可能是存储过程的最后一次执行.
In the UPDATE case, you aren't generating a new IDENTITY, you are just querying, so SCOPE_IDENTITY() won't return the right value. In the UPDATE case, SCOPE_IDENTITY() may appear to work, at first, but it is actually returning the value from the last INSERT in scope (I think), which could be the last execution of the stored proc.
我建议使用 OUTPUT 子句.我试图用你的方法和@usr 的建议做一些工作，但我唯一能做的就是使用 OUTPUT 子句.见下文:
I recommend using the OUTPUT clause. I tried to make something work with your approach, and with @usr's suggestion as well, but the only thing I could make work was to use the OUTPUT clause. See below:
CREATE PROCEDURE dbo.authors_InsertOrUpdate
-- Add the parameters for the stored procedure here
@FirstName nvarchar(50),
@LastName nvarchar(50),
@id int OUTPUT
AS BEGIN
DECLARE @MergeOutput table
(
ACTION VARCHAR(10),
ID INT
);
MERGE Authors AS target
USING (SELECT @id, @FirstName, @LastName) AS source (id, FirstName, LastName)
ON target.FirstName = source.FirstName AND target.LastName = source.LastName
WHEN NOT MATCHED THEN
INSERT (FirstName, LastName) VALUES (source.FirstName, source.LastName)
WHEN MATCHED THEN UPDATE
SET @Id = target.Id
OUTPUT $action, INSERTED.ID INTO @MergeOutput;
SELECT @Id = Id FROM @MergeOutput
END
GO
DECLARE @id2 INT
exec dbo.authors_InsertOrUpdate 'Melvin', 'Smith', @id = @id2 OUTPUT
SELECT @id2
GO
我尝试使用 SCOPE_IDENTITY() 并使用 SET @Id = target.Id 来做一些工作，就像@usr 的回答一样，但我一直在返回最后插入的值，即使是为了更新.
I tried to make something work using SCOPE_IDENTITY(), and using SET @Id = target.Id as in @usr's answer but I kept getting the last inserted value returned, even for an update.
这篇关于存储过程合并(插入或更新)和返回 ID 问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
