Stored Procedure Question - 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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure Question

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-16 : 14:29:19

I need to add info to the database from my frontend website

I have the field TraineeID in two tables (Trainee and TraineeInfo)

It's in the Trainee Table as PK and in the TraineeInfo table as a FK

So I figured I would have to enter it into the Trainee Table first then the TraineeInfo second. Is this stored procedure correct?


ALTER procedure [dbo].[AddTrainee]

@TraineeEmail varchar(75),
@Fname varchar(50),
@Lname varchar(50),
@TimesID int,
@LocID int

AS

set NOCOUNT on 
declare @TraineeID int
DECLARE @TRANCOUNT int
SET @TRANCOUNT = @@TRANCOUNT
Begin Tran TranStart
insert into Trainee(TraineeEmail, Fname, Lname)

values

(@TraineeEmail,
@Fname,
@Lname)
set @TraineeID =SCOPE_IDENTITY() --grabbing the id
if @@error<>0
Begin
Rollback Transaction
Return
End

insert into TraineeInfo(TimesID, LocID)
values
(@TimesID,
@LocID)
select @TraineeID as TraineeID 
if @@error<>0
Begin
Rollback Transaction
Return
End
Commit Tran Transtart

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-16 : 14:50:30

If using SQL 2005 or higher you could change your error handling to a TRY..CATCH. But, if you keep it the same you need adjust your error checking, because it will nto work as you have it. ANY statement affects the value of @@ERROR. So you would need to change to something like:

ALTER procedure [dbo].[AddTrainee]

@TraineeEmail varchar(75),
@Fname varchar(50),
@Lname varchar(50),
@TimesID int,
@LocID int

AS

set NOCOUNT on 
declare @TraineeID int
DECLARE @TRANCOUNT int
DECLARE @Error int

SET @TRANCOUNT = @@TRANCOUNT
Begin Tran TranStart
insert into Trainee(TraineeEmail, Fname, Lname)
values(@TraineeEmail,@Fname,@Lname)

SELECT @Error = @@ERROR, @TraineeID =SCOPE_IDENTITY()
if @Error<>0
Begin
	Rollback Transaction
	-- RAISERROR?
	Return
End

insert into TraineeInfo(TimesID, LocID)
values
(@TimesID,
@LocID)
if @@error<>0
Begin
	Rollback Transaction
	-- RAISERROR?
	Return
End

Commit Tran Transtart

select @TraineeID as TraineeID

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-07-18 : 21:13:45

Okay thanks will try it in the morning and let you know.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources