Error Checking Issue

Hi All,

I have a stored procedure to which I am adding an error checking. Here is my stored procedure.

CREATE PROCEDURE usp_DBGrowth

AS

DECLARE @dbsize DEC(15,2)
DECLARE @logsize DEC(15,2)
DECLARE @dbname SYSNAME
DECLARE @dbsizestr NVARCHAR(500)
DECLARE @logsizestr NVARCHAR(500)
DECLARE @totaldbsize DEC(15,2)
DECLARE @dbid SMALLINT



DECLARE dbnames_cursor CURSOR
FOR
SELECT name, dbid
FROM dbo.sysdatabases

OPEN dbnames_cursor

FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid
WHILE @@FETCH_STATUS = 0
BEGIN


SET @dbsizestr = 'SELECT @dbsize = sum(convert(dec(15,2),size))
FROM ' + @dbname + '.dbo.sysfiles
WHERE fileid = 1'

EXECUTE sp_executesql @dbsizestr, N'@dbsize decimal(15,2) output', @dbsize output
PRINT @dbsize

SET @logsizestr = 'SELECT @logsize = sum(convert(dec(15,2),size))
FROM ' + @dbname + '.dbo.sysfiles
WHERE fileid = 2'


EXECUTE sp_executesql @logsizestr, N'@logsize decimal(15,2) output', @logsize output
PRINT @logsize

SET @totaldbsize = LTRIM(STR((@dbsize + @logsize)*8/1024,15,2))
PRINT @totaldbsize

BEGIN TRANSACTION

IF @dbid IN (SELECT dbid FROM dbo.sysdatabases) AND
@dbid NOT IN (SELECT dbid FROM dbo.databaseoriginalsize)

INSERT INTO databaseoriginalsize (dbid, dbname, dbsize, updatedate) VALUES (@dbid, @dbname, @totaldbsize, getdate())

IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE

COMMIT TRANSACTION

BEGIN TRANSACTION

INSERT INTO databasesize (dbid, updatedate, dbsize) VALUES (5, getdate(), 25)
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE

COMMIT TRANSACTION


FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid

END

CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor


dbid column in the databasesize table is a primary key, so I when I try to insert records with the same dbid, I can an error message that duplicate row can't be inserted. That is fine, however, when I query databasesize table, there are 4 records with the dbid 5. With the error checking I have I thought I shouldn't get any records in the table, since there is an error transaction should be rolled back. Two questions:
Can you tell me what I am doing wrong?
Also, I need to add error checking after execute sp_executesql statement. Any suggestions?