更新存储过程 [英] Update a stored procedure

问题描述

大家好，



我做了一个选择存储过程，在Sql Server 2014中给我一个表。我在vb.net做了一个小程序，我工作了与表。现在，我需要更新该表中的数据。



我的选择存储过程需要5个不同的字段表。我的存储过程的名称是完整的库存



我正在尝试创建更新存储过程（也是一个插入过程）。我不知道如何准确地完成它。



所以是的，真正的问题是，我如何使用存储过程更新多个表？

并且，是否可以更新已经向我返回一个表的select存储过程的字段。



例如：我创建了一个存储过程，返回给我的字段：Name，tel，address。如果我想更新名称中的字段，tel，地址，我是怎么做的？



我是Sql的新手所以不要对我不礼貌！



如果你们可以帮助我，我们将非常感激！



Ps：对不起我的糟糕的英语



我尝试了什么：



这里我尝试更新一张让我退回选择程序的表，似乎不可能？



创建程序dbo.updateInventory

- 在这里添加存储过程的参数

@code int，

@Description nvarchar，

@Qty int，

@location nvarchar，

@Departement nvarchar，

@Company nvarchar，

@owners nvarchar，

@Tax位，

@Price浮动，

@isDevalued位，

@isDeleted位

AS

BEGIN



设置NOCOUNT ON;



--completeInventory是nam我选择存储过程的e



update completeInventory set Code = @ code，Description = @ Description，Qty = @ Qty，Location = @ Location，Departement = @Departement，公司= @公司，所有者= @所有者，税= @税，价格= @价格，isDevalued = @ isDevalued，isDeleted = @ isDeleted

结束

GO

Hi guys,

I make a select stored procedure that returned me a table in Sql Server 2014. I made a little program in vb.net and I work with the table. Now, i need that update the data in that table.

My select stored procedure takes 5 different fields tables. The name of my stored procedure is completeInventory

I'm trying to create a update stored procedure (an insert procedure too).I do not know how to do it exactly.

So yeah, the real question is, how i update multiple table with a stored procedure?
And, is it possible to update the field of a select stored procedure that already returned me a table.

For exemple: I make a stored procedure that returned me the field : Name, tel, address. If i want to update the field in name,tel,adress how i do it?

I'm kind of new with Sql so don't be rude with me!

If you guys can help me it would be very appreciated!

Ps: Sorry for my bad english

What I have tried:

Here i try to update a table that returned me my "Select procedure", it seems to not be possible?

CREATE PROCEDURE dbo.updateInventory
-- Add the parameters for the stored procedure here
@code int,
@Description nvarchar,
@Qty int,
@location nvarchar,
@Departement nvarchar,
@Company nvarchar,
@owners nvarchar,
@Tax bit,
@Price float,
@isDevalued bit,
@isDeleted bit
AS
BEGIN

SET NOCOUNT ON;

--completeInventory is the name of my Select stored procedure

update completeInventory set Code=@code, Description=@Description, Qty=@Qty, Location=@Location,Departement=@Departement,Company=@Company, owners=@owners, Tax=@Tax,Price=@Price, isDevalued=@isDevalued,isDeleted=@isDeleted
END
GO

推荐答案

如何使用存储过程更新多个表？



首先，您应该考虑做一些研究第一。 Code Project有很多关于这个主题的精彩文章。其次，每当您尝试使用单个存储过程更新多个表时，您应该在1个事务中执行此操作，如果有任何问题，则可以将表回滚到之前的状态。下面是一个更新多个表的通用存储过程，如果有问题，将回滚存储过程，以便您的数据不会被破坏。

How to update multiple tables with a stored procedure?

Firstly, you should probably consider doing a little research first. Code Project has a lot of great articles on the subject. Secondly, whenever you try and update multiple tables with a single stored procedure, you should do it in 1 transaction and if there are any problems, then you can rollback the tables to their before state. Below is a generic stored procedure that updates multiple tables and if there is a problem, will rollback the stored procedure so your data doesn't get corrupted.

CREATE PROCEDURE YourProc
(
	@TableID INT
)
AS
BEGIN
	BEGIN TRY
		BEGIN TRANSACTION;
			UPDATE Table1
			SET SalesLastYear = SalesLastYear + @SalesAmt
			WHERE Table1ID = @TableID;

			UPDATE Table2
			SET SalesLastYear = SalesLastYear + @SalesAmt
			WHERE Table2ID = @TableID;

			UPDATE Table3
			SET SalesLastYear = SalesLastYear + @SalesAmt
			WHERE Table3ID = @TableID;

		COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
    		IF @@TRANCOUNT > 0
    			ROLLBACK TRANSACTION;
 
    			DECLARE @ErrorNumber INT = ERROR_NUMBER();
    			DECLARE @ErrorLine INT = ERROR_LINE();
 
    			PRINT 'Error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
    			PRINT 'Line number: ' + CAST(@ErrorLine AS VARCHAR(10));
 
    		THROW;
  	END CATCH
END;

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