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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy