插入完成后如何更新... [英] How to update when insert completed...

问题描述

create Proc insert_Returns
@RID int output,
@ReturnsType int,
@CSPrefix varchar(50)
AS
begin
if not exists(select ReturnsType from tbl_ReturnSetings where RID=@RID)
begin 
INSERT [dbo].[tbl_ReturnSetings]
(
	[ReturnsType],
	[CSPrefix]
)
VALUES
(
	@ReturnsType,
	@CSPrefix
)
	SELECT @RID=SCOPE_IDENTITY();
	end
	else
	begin
	update tbl_ReturnSettings
	 SET	CSPrefix = @CSPrefix 
	where RID=@RID
end
end

请帮助我

当我在上面的程序中插入新值时工作正常但返回类型是存在CSprefix没有更新......

Pls help me
When am insert new values above procedure working fine but the ReturnsType is exists CSprefix not updated ......

推荐答案

由于您没有描述有关该问题的任何细节，我假设您想在完成插入操作后更新某些内容。如果是这样，您可以使用插入触发器后。请参阅以下链接以供参考



在Sql Server中触发 [ ^ ]



问候......：笑：

As you have not described any details regarding the problem,i assume you want to update something after completion of insert operation.If this is so,you can make use of After Insert Trigger.Refer to below link for reference

Triggers in Sql Server[^]

Regards.. :laugh:

重新缩进代码，使其更清晰：

Re-indenting your code so it is clearer:

create Proc insert_Returns
@RID int output,
@ReturnsType int,
@CSPrefix varchar(50)
AS
begin
   if not exists(select ReturnsType from tbl_ReturnSetings where RID=@RID)
   begin 
      INSERT [dbo].[tbl_ReturnSetings](
	[ReturnsType],
	[CSPrefix])
      VALUES(
	@ReturnsType,
	@CSPrefix)
      SELECT @RID=SCOPE_IDENTITY();
   end
   else
   begin
      UPDATE tbl_ReturnSettings
      SET CSPrefix = @CSPrefix 
      WHERE RID=@RID
   end
end

使其更加明显。



但是你所要求的是没有意义的 - 如果没有记录，你的SP会执行INSERT， 或 如果已经存在则执行UPDATE。 />


但你的插入看起来使用IDENTITY字段 - 在这种情况下你不能提供ID值（所以可能会传递一个负值以确保它插入）。如果它插入，那么更新没有意义，因为它更新你刚刚插入的具有相同值的字段正在更新...



我觉得你需要坐下来思考你想要做什么，因为我很确定你要求的不是它......

Makes it a bit more obvious.

But what you are asking for is pointless - your SP does an INSERT if no record exists, or does an UPDATE if it is already present.

But your insert looks to use an IDENTITY field - in which case you cannot provide an ID value (So are presumably passing a negative value to ensure it does an insert). And if it does an insert, then there is no point in doing the update, because it updates the field you just inserted with the same value are are updating...

I think you need to sit down and think about what you are trying to do, because I'm pretty sure what you have asked for is not it...

你为什么要设置在你的where语句中使用时输出@RID。

你不认为你应该将它传递给你SP。



请尝试以下代码..

why are you setting @RID output when you are using it in your where statement.
don't you think you should pass it to you SP.

Try below code..

create Proc insert_Returns
@RID int,
@ReturnsType int,
@CSPrefix varchar(50)
AS
begin
   if not exists(select ReturnsType from tbl_ReturnSetings where RID=@RID)
   begin 
      INSERT [dbo].[tbl_ReturnSetings](
	[ReturnsType],
	[CSPrefix])
      VALUES(
	@ReturnsType,
	@CSPrefix)
      SELECT @RID=SCOPE_IDENTITY();
   end
   else
   begin
      UPDATE tbl_ReturnSettings
      SET CSPrefix = @CSPrefix 
      WHERE RID=@RID
   end
end

我认为现在应该可以正常使用...

I think now it should work fine for you...

这篇关于插入完成后如何更新...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！