TRANSACTION Help - 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)

TRANSACTION Help

Author

Topic

Swoosh
Starting Member

6 Posts

Posted - 2004-02-14 : 17:41:01

I have posted 3 tables and my stored procedure below. To test the stored procedure, I created a membership record and then I did a select to find what the member's ID was. I then called the stored procedure as listed below. However, in the printout I see it continues on to eventually attempt to commit the transaction, even though I figured it shouldn't have gotten to that point. Can someone tell me why it gets that far? Should I better structure my stored procedure?

After BEGIN TRAN - Inserting to Folder table
Server: Msg 515, Level 16, State 2, Procedure CreateUserFolder, Line 35
Cannot insert the value NULL into column 'Name', table 'Yahoo.dbo.Folders'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Checking for errors
No errors detected, moving on to create folderstorage records
Getting folder ID: 18
Attempting insert into folderstorage
Server: Msg 547, Level 16, State 1, Procedure CreateUserFolder, Line 65
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_FolderStorage_Folders'. The conflict occurred in database 'Yahoo', table 'Folders', column 'FID'.
The statement has been terminated.
Checking for errors
At the end of CreateUserFolder - Everything appears OK - COMMITTING TRANSACTION
Returing @FolderID: 18

insert into membership values ('Bill', 'Gates', 'Geek', 'Uberslick', GETDATE(), GETDATE())
select * from membership
exec createuserfolder 18, NULL

CREATE TABLE [Membership] (
[MID] [int] IDENTITY (1, 1) NOT NULL ,
[Firstname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Lastname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Username] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Password] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RegisteredDate] [datetime] NULL ,
[LastLogin] [datetime] NULL ,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[MID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [Folders] (
[FID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[LastModified] [datetime] NOT NULL ,
CONSTRAINT [PK_Folders] PRIMARY KEY CLUSTERED
(
[FID]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [FolderStorage] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[MID] [int] NOT NULL ,
[FID] [int] NOT NULL ,
CONSTRAINT [PK_FolderStorage] PRIMARY KEY CLUSTERED
(
[MID],
[FID]
) ON [PRIMARY] ,
CONSTRAINT [FK_FolderStorage_Folders] FOREIGN KEY
(
[FID]
) REFERENCES [Folders] (
[FID]
),
CONSTRAINT [FK_FolderStorage_Membership] FOREIGN KEY
(
[MID]
) REFERENCES [Membership] (
[MID]
)
) ON [PRIMARY]
GO

PROCEDURE dbo.CreateUserFolder

@UserID int,
@FolderName varchar(255)

AS

-- This will hold the FID of the folder after insertion.
DECLARE @FolderID int

-- Begin a new transaction that we can rollback if something goes wrong.
BEGIN TRAN

Print 'After BEGIN TRAN - Inserting to Folder table'

-- Create the folder record.
INSERT INTO folders VALUES (@FolderName, GETDATE(), GETDATE())

Print 'Checking for errors'

-- Only proceed further if no errors were found.
If @@ERROR <> 0
BEGIN

Print 'Error Detected: ROLLING BACK TRANSACTION'

-- There was an error, rollback the transaction.
ROLLBACK TRAN

Print 'Returning -1'

-- We return -1 to signify an error occurred.
RETURN -1

END

Print 'No errors detected, moving on to create folderstorage records'

-- Get the FID of the inserted folder.
SELECT @FolderID = @@IDENTITY

Print 'Getting folder ID: ' + STR(@FolderID)

Print 'Attempting insert into folderstorage'

-- Create a FolderStorage entry. This maps a folder ID to a member ID.
INSERT INTO folderstorage VALUES (@UserID, @FolderID)

Print 'Checking for errors'

-- Only proceed further if no errors were found.
IF @@ERROR <> 0
BEGIN

Print 'Error Detected: ROLLING BACK TRANSACTION'

-- There was an error, rollback the transaction.
ROLLBACK TRAN

Print 'Returning -1'

-- We return -1 to signify an error occurred.
RETURN -1
END

Print 'At the end of CreateUserFolder - Everything appears OK - COMMITTING TRANSACTION'

-- Commit the tranaction if we get to this point.
COMMIT TRAN

Print 'Returing @FolderID: ' + STR(@FolderID)

-- Return the FID field of the created folder record.
RETURN @FolderID

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-02-15 : 04:58:28

It's your PRINT statements. The @@ERROR variable holds the error generated by the last SQL statement, so the print statement basically eats away the actual error. Comment your print statements, or move them to after the error checks.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources