从存储过程更新数据库表 [英] update DB table from stored procedure

问题描述

create procedure update_sam1(@control_id as varchar) as
begin
update Table_2 set Current_Value=10 where Control_Id=1

end

您好，我只是尝试使用存储过程来更新表中的"Current_Value"，但表没有得到更新...存储过程的新手...请帮助...

hi im just trying to update a "Current_Value" in the table using stored procedure but the table is not getting updated... im new to stored procedure... please help...

推荐答案

在更新查询之前和之后放置选择语句，以验证结果

Put select statement before and after update query, to validate the result

create procedure update_sam1(@control_id as varchar) as
begin
select Current_Value, Control_Id from Table_2 where Control_Id=1
update Table_2 set Current_Value=10 where Control_Id=1
select Current_Value, Control_Id from Table_2 where Control_Id=1
end

我想Current_value和Control_Id字段是数字(整数).

试试这个:

I suppose Current_value and Control_Id fields are numerics (integers).

Try this:

CREATE PROCEDURE UpdateCurrentValueById 
	-- Add the parameters for the stored procedure here
	@ci int = 0, 
	@cv int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	UPDATE Table2 SET [Current_Value] = @cv WHERE [Control_Id] = @ci
	--return updated value
	SELECT @cv = [Current_Value] FROM Table2 WHERE [Control_Id] = @ci
END
GO

您是否真的要在过程的WHERE子句中使用静态值.我的意思是说，不是

Do you really want to use a static value in your procedure''s WHERE clause. What I mean is that instead of

create procedure update_sam1(@control_id as varchar) as
begin
   update Table_2 set Current_Value=10 where Control_Id=1
end

你应该有

should you have

create procedure update_sam1(@control_id as varchar) as
begin
   update Table_2 set Current_Value=10 where Control_Id=@control_id
end

因此，传递的参数将控制要更新的行.不知道这是您要查找的内容，但目前您根本不使用该参数.

So the parameter passed would control what row is updated. Don''t know if this is what you''re after but currently you don''t use the parameter at all.

这篇关于从存储过程更新数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！