Click here to Skip to main content
15,917,481 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
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
Posted
Updated 9-Nov-16 17:04pm
v2
Comments
[no name] 9-Nov-16 16:22pm    
"I do not know how to do it exactly", you do not know how to do what exactly? Looks like you already have a stored procedure so what is the question?
Patrice T 9-Nov-16 16:25pm    
Nothing in SQL documentation ?
Member 12754823 9-Nov-16 16:27pm    
The real question is how i update multiple table with a stored procedure!

1 solution

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;
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900