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
- if we can write a better query, if so how?
- 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