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