Insert Statements in Stored Procedure

[tis9700]

Hi Gurus,
I'm trying to learn more about Stored Procedures. I don't have alot of time to practice with them but I created a Stored Procedure with two insert statements. It's supposed to perform inserts on two tables in a parent/child one-to-many relationship but I'm not sure if I have it right. I'm afraid some of the text I read was a little vague. If I can get the SP correct I plan to call it within ADO.NET with parameters mapped to textboxes, etc on a Window Form. The purpose of the form is Data Entry only. So I just want to make sure my SP is correct before I proceed.

Am I on the right track? What else should I consider? Really appreciate any guidance you can give me!

Thanks,
Tracy


ALTER PROCEDURE [usp_INSERT_NEW_CLASS]
(@InstID [int],
@ClassDate [smalldatetime],
@ClassCountyCode [char](2),
@ClsTltHrsTaught [smallint],
@ClsEnrolled [smallint],
@ClsGraduated [smallint],
@ClsSchoolHrs [bit],
@ClsMales [smallint],
@ClsWhite [smallint],
@ClsBlack [smallint],
@ClsNativeAm [smallint],
@ClsHispanic [smallint],
@ClsOther [smallint],
@ClsComment [varchar](100),
@AsstSSN [Char] (9),
@ClassID [int] OUTPUT)

AS
BEGIN
SET NOCOUNT ON
INSERT INTO [HUNTER_SAFETY_PROGRAM].[dbo].[CLASSES]
( [InstID],
[ClassDate],
[ClassCountyCode],
[ClsTltHrsTaught],
[ClsEnrolled],
[ClsGraduated],
[ClsSchoolHrs],
[ClsMales],
[ClsWhite],
[ClsBlack],
[ClsNativeAm],
[ClsHispanic],
[ClsOther],
[ClsComment])

VALUES
( @InstID,
@ClassDate,
@ClassCountyCode,
@ClsTltHrsTaught,
@ClsEnrolled,
@ClsGraduated,
@ClsSchoolHrs,
@ClsMales,
@ClsWhite,
@ClsBlack,
@ClsNativeAm,
@ClsHispanic,
@ClsOther,
@ClsComment)

SELECT @ClassID = SCOPE_IDENTITY()

INSERT INTO [HUNTER_SAFETY_PROGRAM].[dbo].[ASSISTANTS]
( [AsstSSN],
[ClassID])
VALUES
(@AsstSSN,
@ClassID)
END

 

[bborissov]

If this is a Parent-Child relationship you must put these inserts in transaction, just in case something happens:

CREATE .....
AS 
BEGIN 
  BEGIN TRANSACTION
  --- first insert goes here
  IF @@ERROR <> 0
     BEGIN
         ROLLBACK 
         RETURN
     END
  --- second insert goes here
  IF @@ERROR <> 0
     BEGIN
         ROLLBACK 
         RETURN
     END
  COMMIT TRANSACTION
END

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[tis9700]

Hi Borislav,
Thanks for responding. I kind of thought that might be the case. I'll give it a try. One question! I tested my previous SP in QA by opening the SP and providing the values for the parameters but that only allows me to test for results of one row in the child table. Is there a better way of testing the SP? Perhaps with Exec statements and values?
Thanks again,
Tracy

 

[SQLSister]

always test by using the exec statement and values, otherwise you wouldn't find out if there was a problem with your input parameter definitions. This proc will only create one record in each table. If this is what you intended to do, the overall process looks fine once you add the transaction processing. If you have null values possible for some of the input parameters make sure to test that as well.

"NOTHING is more important in a database than integrity." ESquared

 

[bborissov]

As far as I can see in that SP you always INSERT one row,. if you want to see how it will react for multiple rows you could put in in a loop, something like:

In QA

DECLARE @i int
SET @i = 0
WHILE @i<21 
      BEGIN
          EXEC usp_INSERT_NEW_CLASS (parameters go here)
          SET @i = @i + 1
     END

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[tis9700]

Hi SQLSister,
That's not really what I intended. I wanted the first INSERT statement to create one parent record and the second INSERT statement to create one or more child records. Since there is one class with one or more Assistants. Sounds like I'm not on the right track after all!

 

[tis9700]

Hey Borislav,
I'm goning to try putting that Loop on the second statement.

 

[bborissov]

tis9700,
From your answer to SQLSister i understood that you want to add one record to parent and one or many records to child, isn't it? If tits is the case THAT SP wouldn't work. It always insert a record in parent AND in the child.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[tis9700]

Hi Borislav,
What if I break it up into two SPs and executed the child sp inside the parent sp within a loop? Will that work? How do you insert into two tables with a parent/child one-to-many relationship?
Thanks,
Tracy

 

[SQLSister]

You could put in a loop but it will not put in more than one record anyway because there is only one import value for @AsstSSN as it is being passed as an input variable. I fyou want to send in several you will either need muliple input varaibles or send in an input string which would then have to be parsed into a temp table and then you would do the insert to the second table cross joining to the temp table with all the possible values of AsstSSN. There are some FAQs here on dealing with splitting up a delimted string. I suggest you check them out before trying to do this.

"NOTHING is more important in a database than integrity." ESquared

 

[tis9700]

Thanks SQLSister for the info. Really appreciate the guidance.

And Thanks to Borislav for his input.

I learn something new everyday! Gotta love it.