Committed Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Committed Data

Author

Topic

TSQLMan
Posting Yak Master

160 Posts

Posted - 2006-02-22 : 10:32:45

I Have created the following procedure. In the procedure I insert data into 1 table ("--Insert 1") and then the second Insert ("--Insert 2") Inserts Data into a another table depending on data that was inserted in the first Insert. I have done this in the past, but have been told that this is not a good practice. This application will get no more than 50 transactions per day (It is on a rather busy SQL Server). What would be a better way to handle this?

CREATE PROCEDURE p_InsertObservationWithDetail

@PersonID INT,
@ObservationDate DATETIME,
@CompanyID INT,
@LocationID INT,
@SectionID INT,
@JobID INT,
@Comment VARCHAR(5000)

AS

DECLARE @UpdateDTM DATETIME
SET @UpdateDTM = (SELECT GETDATE())

--Insert 1

INSERT INTO tObservations
(PersonID, ObservationDate,CompanyID, LocationID, SectionID,JobID, Comment,UpdateDTM)

VALUES (@PersonID, @ObservationDate, @CompanyID, @LocationID, @SectionID,@JobID, @Comment, @UpdateDTM)

IF @@ROWCOUNT > 0

--Insert 2
BEGIN
INSERT INTO tObservationDetail
(ObservationID, CheckID)

SELECT o.ObservationID, mm.CheckID
FROM tObservations o
INNER JOIN tSafetyCheckMM mm ON o.JobID = mm.JobID
WHERE o.UpdateDTM = @UpdateDTM and o.JobID = @JobID AND mm.JobID = @JobID
END

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-22 : 10:43:42

I don't see an issue with performing multiple selects in the same proc.

That said, I'd wrap these insert statements in a transaction and do a little error check.

TSQLMan
Posting Yak Master

160 Posts

Posted - 2006-02-22 : 11:08:45

so selecting data, that you just inserted in the same procedure is not a problem? I have added the error trapping as well.

Thanks, for your input.

TSQLMAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources