Inserting in Multi table With one Store Procedure

future3839

Hi, I have a form which including different sort of information. for instance, some information is regarding to personal data which related to Table1 and some are for Table2 and the some are for Table3. all this information are in 1 form. Now, I just wondering to know, when I wanna insert data via Store procedure do I have to write SP for each of them? is there any approach to insert data with writing just one SP. how can I write SP, inserting data in Multi table. is it possible

PIEBALDconsult

If you insist on using stored procedures then there should be one that performs all the required statements in a transaction that can be rolled back. Many practitioners seem to forget that by definition a procedure should contain more than one statement.

Mycroft Holmes

future3839 wrote:

is there any approach to insert data with writing just one SP

This is a basic aspect of an SP Receive values via variables in the procedure declaration

CREATE PROC StoreDumy
-- Declare
@Var1 VARCHAR(100),@Var2 VARCHAR(100),@Var3 VARCHAR(100),@Var4 VARCHAR(100),
@Var5 VARCHAR(100),@Var6 VARCHAR(100),@Var7 VARCHAR(100)

Insert the variables in the tables required. You may want to wrap the inserts in a transaction so that if 1 insert fails all inserts are rolled back and you are not left with orphaned data

As

DECLARE @ID int

begin TRAN
INSERT dbo.Country (Fld1, Fld2)
VALUES(@Var1, @Var2)

SET @ID = SCOPE\_IDENTITY()

INSERT dbo.State (CtryID, Fld1, Fld2)
VALUES(@ID, @Var3,	@Var4)

SET @ID = SCOPE\_IDENTITY()

INSERT dbo.Town (StateID, Fld1, Fld2)
VALUES(@ID, @Var5,	@Var6)

COMMIT TRAN

Never underestimate the power of human stupidity RAH