Update a stored procedure

Question

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 9-Nov-16 10:11am

Member 12754823

Updated 9-Nov-16 17:04pm

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

jgakenhe · Answer 1 · 2016-11-09T17:04:00

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;