Is transaction on single insert required

1

I would just need to insert a row into the database table and return two output parameters.
Could anyone please review the following script and let me know

  1. if we can write a better query, if so how?
  2. do we really need a transaction for one insert

ALTER PROC [dbo].[usp_SubmissionHeaderInsert]
(
@FormId uniqueidentifier,
@FormCode varchar(10),
@FormTitle varchar(200),
@User varchar(50),
@Url varchar(255) = NULL,
@Host varchar(50),
@RemoteHost varchar(50) = NULL,
@UserAgent varchar(255) = NULL,
@Referrer varchar(255) = NULL,
@SubmissionId uniqueidentifier out,
@SubmitSequence varchar(30) out
) AS

BEGIN TRY
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

DECLARE @SubmitNumber int
SELECT @SubmitNumber = ISNULL(MAX(SubmitNumber),0) + 1
FROM [dbo].[SubmissionHeader]
WHERE FormCode = @FormCode

DECLARE @InsertedHeader TABLE (SubmissionId uniqueidentifier, FormCode varchar(10), SubmitNumber bigint)

INSERT INTO [dbo].[SubmissionHeader]
([FormId],[FormCode] ,[SubmitNumber] ,[FormTitle] ,[User], [Url] ,[Host], [RemoteHost] ,[UserAgent] ,[Referrer])

OUTPUT inserted.SubmissionId, inserted.FormCode, inserted.SubmitNumber
INTO @InsertedHeader (SubmissionId, FormCode, SubmitNumber)
SELECT
@FormId,
@FormCode,
@SubmitNumber,
@FormTitle,
@User,
@Url,
@Host,
@RemoteHost,
@UserAgent,
@Referrer

COMMIT TRAN

SELECT
@SubmissionId = SubmissionId,
@SubmitSequence = CONVERT(varchar(30),[FormCode] + '-' + CONVERT(varchar(30), SubmitNumber))
FROM @InsertedHeader

END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN

DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int, @ErrNum int
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY(), @ErrNum = ERROR_NUMBER()		
RAISERROR('dbo.usp_SubmissionHeaderInsert - %d: %s', @ErrSeverity, 1, @ErrNum, @ErrMsg)

END CATCH

2

You could combine the select statement that finds the next SubmitNumber with the insert statement as shown below. Since it is a single statement, you wouldn't then need to open an explicit transaction.

INSERT  INTO [dbo].[SubmissionHeader]
        ( [FormId] ,
          [FormCode] ,
          [SubmitNumber] ,
          [FormTitle] ,
          [User] ,
          [Url] ,
          [Host] ,
          [RemoteHost] ,
          [UserAgent] ,
          [Referrer]
        )
OUTPUT  inserted.SubmissionId ,
        inserted.FormCode ,
        inserted.SubmitNumber
        INTO @InsertedHeader ( SubmissionId, FormCode, SubmitNumber )
SELECT  @FormId ,
        @FormCode ,
        ISNULL(MAX(SubmitNumber), 0) + 1 ,
        @FormTitle ,
        @User ,
        @Url ,
        @Host ,
        @RemoteHost ,
        @UserAgent ,
        @Referrer
FROM  [dbo].[SubmissionHeader]
WHERE   FormCode = @FormCode;