September 27, 2005 at 4:22 pm
Is there an implicit transaction when only doing one insert statement in a stored procedure? I currently code my stored procedures like the example below, but I was told I didn't need the transaction since I only had one insert statement. I know that if I don't create the transaction like I currently do Identity columns lose the number if the insert fails, but I don't know of anything else that happens.
Can someone point me to something or give me a short reason why I should or shouldn't use the transaction?
Thanks Kris
SAMPLE SP:
CREATE PROCEDURE dbo.uspBillerInsert (
@BillerName AS VARCHAR(50),
@BillerParentId AS BIGINT = NULL,
@BillerGroupId AS SMALLINT = NULL,
@Street1 AS VARCHAR(50) = NULL,
@Street2 AS VARCHAR(50) = NULL,
@City AS VARCHAR(50) = NULL,
@StateCode AS VARCHAR(2) = NULL,
@CountryCode AS VARCHAR(50) = NULL,
@PostalCode AS VARCHAR(10) = NULL,
@CreatedBy AS VARCHAR(100) = NULL,
@BillerId AS BIGINT OUTPUT) AS
DECLARE @ErrorNumber AS INT
SET NOCOUNT ON
BEGIN TRANSACTION
INSERT INTO dbo.Biller (BillerParentId, BillerGroupId, BillerName,
Street1, Street2, City, StateCode, CountryCode,
PostalCode, Created, CreatedBy)
VALUES (@BillerParentId, @BillerGroupId, @BillerName, @Street1,
@Street2, @City, @StateCode, @CountryCode, @PostalCode, GETDATE(),
@CreatedBy)
SELECT @ErrorNumber = @@ERROR, @BillerId = SCOPE_IDENTITY()
IF (@ErrorNumber <> 0) GOTO TransactionSection
TransactionSection:
IF (@@TRANCOUNT > 0) BEGIN
IF (@ErrorNumber <> 0) BEGIN
ROLLBACK TRANSACTION
END ELSE BEGIN
COMMIT TRANSACTION
END
END
SET NOCOUNT OFF
September 28, 2005 at 8:21 am
Without using BEGIN and COMMIT etc, each DML statement is a transaction in itself.
You can use SET IMPLICIT_TRANSACTIONS ON to do an implicit BEGIN TRAN if you wanted
In your case, you are are doing it to preserve the ID col values - so you will have to continue this way.
That is your only reason, because it sounds like gaps in your id values are not acceptable.
What are you doing with the @billerid anyway?
September 29, 2005 at 12:12 pm
Basically all I was wondering is what are differences between using or not using the transaction block code in the stored procedure. The only difference I could see was that my identity column sequence would produce gaps.
Would it be a best practice to include or exculde the BEGIN TRANSACTION statement?
Thanks, Kris
September 30, 2005 at 12:40 am
In this case, just remove the transaction handling - it's just overhead
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply