[T-SQL] Does a "Begin... End" in a While loop act as a transaction?

---

Does a "Begin... End" in a While loop act as a transaction? Posted: 29 Apr 2013 12:23 AM PDT Reading Lynns comments in this topic: [url=http://www.sqlservercentral.com/Forums/Topic1447456-391-1.aspx]http://www.sqlservercentral.com/Forums/Topic1447456-391-1.aspx[/url], I found myself wondering if a script I've got to re-org or re-build indexes was going to use transactions, or be one big transaction.The code in question reads the tables that need to be re-orged or re-built from a temp table, and loops through until every row in the temp table has been read.So it's similar to:[code="sql"]Set @loopcnt = 0While @loopcnt != (select COUNT(DISTINCT TableName) from #IndicesToReorganize) Begin Set @loopcnt = @loopcnt + 1 set @tblName = (Select tabletowork from (Select TableName as tabletowork, ROW_NUMBER() OVER(ORDER BY TableName) as rn from #IndicesToReorganize) tmp Where rn = @loopcnt) Set @Alter = N'alter index ALL on ' + @dbname + '.dbo.' + @tblName + ' REORGANIZE' exec(@Alter) End[/code]So, do those Begin / Ends inside the While run each reorg as a transaction, or do I need to wrap the "exec(@Alter)" in a "Begin Transaction / End Transaction"?Taking a look in BOL, it looks like, NO they do not function as a begin / end transaction, and I will need to put such in the loop.Correct?Thank you,Jason

error in Transactions... need urget help Posted: 28 Apr 2013 07:45 PM PDT hi, i have below code[code="sql"]declare @V_AreaId int = 1declare @V_Area nvarchar(30)='xxx'declare @V_Pincode nvarchar(10) ='35646'BEGIN TRY BEGIN TRANSACTION IF NOT EXISTS (SELECT 1 FROM Mas_Area WHERE AreaId = @V_AreaId) BEGIN IF EXISTS(SELECT * FROM Mas_Area WHERE Area = @V_Area) BEGIN SELECT 'Area already exists' AS Msg RETURN; Print @@TRANCOUNT END ELSE IF EXISTS(SELECT * FROM Mas_Area WHERE Pincode = @V_Pincode) BEGIN SELECT 'Pincode already exists' AS Msg RETURN; Print @@TRANCOUNT END ELSE BEGIN INSERT INTO Mas_Area (Area,DistrictId,Pincode, CreatedBy, CreatedOn , Status, Reason) VALUES (@V_Area,12,@V_Pincode, 'dhii', GETDATE(), 'true', null) SELECT @V_AreaId = IDENT_CURRENT('Mas_Area') Print @@TRANCOUNT END END ELSE BEGIN IF EXISTS(SELECT * FROM Mas_Area WHERE AreaId <> @V_AreaId AND Area = @V_Area) BEGIN SELECT 'Area already exists' AS Msg RETURN; END ELSE IF EXISTS(SELECT * FROM Mas_Area WHERE AreaId <> @V_AreaId AND Pincode = @V_Pincode) BEGIN SELECT 'Pincode already exists' AS Msg RETURN; END ELSE BEGIN UPDATE Mas_Area SET Area = @V_Area, Pincode=@V_Pincode WHERE AreaId = @V_AreaId END END SELECT @V_AreaId, 'SUCCESS' AS Msg COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION SELECT ERROR_NUMBER() as ErrorNumber,ERROR_MESSAGE() as ErrorMessage; INSERT INTO ERRORLOG (PatientNumber, SP_Name, ErrorNo, ErrorMsg, ErrorDt, ModuleName, FormName) VALUES ('NA','USP_Mas_Area_InsertUpdate', ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE(), 'Masters', 'Area') END CATCH[/code]when i run this for second time I am getting Error ''''Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.''no of begin and end is Equal but still getting error Please need help.thanks,

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610