Stored Procedure for Multiple Table Insert

[kafka]

This stored procedure does the following. Table 1 has a field caled id1. It is the primary key and is an autogenerated incremeneting in an ascending order by 1.

Table 2 has a primary key called id2. The primary key is an autogenerated number incremeneting in an ascending order by 1. It has a foreign key called id1, which is the primary key of Table 1.

So this is how the scenario works out. A HTML form submits to the database. The application logic inserts a record into Table 1 and Table 2 at the same time. As it writes to Table 2 I need the Stored Procedure to grab the last primary key id from Table 1 and insert it as the candidate key. The result would look like the following as records are inserted into both tables


record 1 for Table 1 record 1 for Table 2
id1 id2 id1
1 1 1
record 2 for Table 1 record 2 for Table 2
id1 id2 id1
2 2 2

record 3 for Table 1 record 3 for Table
id1 id2 id1
3 3 3


Here is exactly how I wrot the Stored Procedure. Attached is the error message I got.

CREATE PROCEDURE "SP1"

(@P_Var1 int,
@P_Var2 int)

As

Declare @L_PrimaryID int

Begin Trans

Insert into Table2 (Field1) Values (id1)

SET @L_PrimaryID = Cast(@@IDENTITY as int)

If CAST(@@Error as int) > 0 ErrHandler

Insert into Table2 (field2) Values (@L_PrimaryID)

If CAST(@@Error as int) > 0 ErrHandler



Commit trans

Return 0

ErrHandler:

Rollback Trans



Here are the the errors I get:
Error 170: Line 10: Incorrect syntax near 'Trans'
The name 'id1' is not permitted in this context
Line 16: Incorrect syntax near 'ErrHandler'
Line 20:Incorrect syntax near 'Err Handler'
Incorrect syntax near 'Trans'
Incorrect syntax near 'Trans'

So I guess I goofed on the syntax, but is the logic of this SP kosher? Any suggestions to make this darn thing work would be appreciated. Thanks for all your help.



-G

 

[rac2]

this is the line from your stored procedure that gives the error.

Insert into Table2 (Field1) Values (id1)

Assuming that @P_Var1 and @P_Var2 are the data values you want to insert into Table1 and Table2 respectively
It seems that the statement should be

Insert into Table1 (Field1) Values ( @P_Var1 )

and the second INSERT should be

Insert into Table2 (field2, foreign_key_id_field_name) Values (@P_Var2, @L_PrimaryID)

 

[tlbroadbent]

You have so many errors, it hard to know where to begin.

1) Spell out Transaction don't use Trans.

2) Transaction isn't required on the Commit and Rollback statements.

3) There is no need to CAST @@identity or @@error as INT. Both are already INT.

4) id1 in the first INSERT statement is unknown. Did you mean to use one of he variables that the SP can accept?

5) You must include to GOTO statement in order to go to the error handler. It is not a sun routine that can be called by merely mentioning the name.

6) The first error test occurs after capturing the @@identity value. Thus it won't capture the error.

The following contains corrected syntax. It is probably closer to what you need than your original query. It should provide a basis you can build on.

CREATE PROCEDURE spTest
(@P_Var1 int, @P_Var2 int)
As

Declare @L_PrimaryID int

Begin Transaction

Insert into Table2 (Field1) Values (@p_var1)

If @@Error > 0 goto ErrHandler

SET @L_PrimaryID = @@IDENTITY

Insert into Table2 (field2) Values (@L_PrimaryID)

If @@Error > 0 goto ErrHandler

Commit

Return 0

ErrHandler:

Print 'Whoops! An error occurred. ROLLBACK.'

Rollback
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.