how can i insert data in multiple table in one store procedure?

Question

0.00/5 (No votes)

See more:

hi i want to insert data on one button click in multile tables

i have tables like

1) Tbl_Product
pid,
pname,

2)Tbl_Schedule
sid,
pid,
startdate,
closedate,

3)Tbl_Brand
bid
pid,
brandName

now i want to insert in this three table
i used storeprocedure like

SQL

insert into Tbl_Product values (@pname);

declare @pid=@@identity;

insert into Tbl_Schedule values (@pid,@sdate,@cdate);

insert into Tbl_Brand values (@pid,@brandName);

help me
thanks....

Posted 31-Mar-12 1:36am

PKriyshnA

Add a Solution

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)

Mohamed Mitwalli · Accepted Answer · 2012-03-31T04:22:00

Solution 1

HI ,
This will give an idea

SQL

CREATE PROCEDURE [dbo].[usp_Tbl_Product_Insert]
(
 @pnam varchar(50) ,@sid INT,
 @startdate DATETIME,
 @closedate DATETIME,  
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION 
INSERT INTO Tbl_Produc
(
   COL NAME
 )
values
( 
    @pnam
 )
DECLARE @pid  INT 
SET @pid  = SCOPE_IDENTITY()
IF @@ERROR = 0
		BEGIN 
		INSERT INTO Tbl_Schedule
		(COL NAME)
		VALUES
		(@pid ,@startdat,@closedate )
		
		iF @@ERROR = 0
			BEGIN
			 INSERT INTO Tbl_Brand
			(
				COL NAMES
		    ) 
				VALUES
			(
				@pid,@brandName
			 )
 		 END	
				
				IF @@ERROR = 0
				BEGIN			 
					COMMIT TRANSACTION
				END
			ELSE
				BEGIN			 
					ROLLBACK TRANSACTION		
				END
		END
ELSE
		BEGIN
			ROLLBACK TRANSACTION
		END
 
END

Best Regards
M.Mitwalli

Posted 31-Mar-12 4:22am

Mohamed Mitwalli

Comments

PKriyshnA 31-Mar-12 23:16pm

i got solution but i can not understand this query...
can any one describe....please

Mohamed Mitwalli 1-Apr-12 2:07am

Hi ,
how are you ?
Well the procedure has same structure of normal procedure
1- CREATE PROCEDURE "PROCEDURE NAME" (preferred don't start your own procedure with "SP" because of the SOL will go first and search on system Stored procedure so there is wasting of your time So don't do it).
2- Then the Body of Procedure Start with begin and end with End
3- SET NOCOUNT ON; the count is not returned it will improve performance for your procedure
4- when u are saying BEGIN TRANSACTION it means u stop auto Commit in your transaction because maybe it will happen failure in one in ur insert and you don't want some of your insert data execute right and other have problem your data with be not accurate, try to read about (ACID in SOL) .
5- checking on @@ERROR if not equal 0 so it must happen error so i have Rollback .
Hope it help you
Best Regards
M.Mitwalli