Databases
StackOverflow2013
Postsdbo
POSQL transactions / error handling / updates
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL transactions / error handling / updates
    primarykey
    data
    text
    <p>I have a bit of a complex question. I have a stored procedure that takes no parameters. Currently when I run it I get an error message telling me that </p> <blockquote> <p><em>after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.</em></p> </blockquote> <p>In essense the stored procedure looks something like this:</p> <pre><code> USE FirstDatabase GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [schema].[Procedure] AS SET NOCOUNT ON SET XACT_ABORT ON BEGIN TRY DECLARE @WholeBunchOfVariables varchar(max); --All these are ofcourse separated and different data types IF(SELECT OBJECT_ID('tempdb..#tempTable')) IS NOT NULL BEGIN DROP TABLE #tempTable END CREATE TABLE #tempTable ( ..., --bunch of fields to be populated IsProcessed bit) -- this field is used later to run through a temp table variable one record at a time, once done, this is set to 1 DECLARE @tempTableVar TABLE ( ...) --Insert data from the accounts table(s) into the temp table. Only process data --that has been modified in the past day. INSERT INTO #tempTable SELECT DISTINCT ... -- bunch of fields from different tables FROM SomeTables --Loop through the data in the temp table, one record at a time. Process it and --once done, set the IsProcessed field in the temp table to 1 so that it can pick --up the next record to process. WHILE (1=1) BEGIN INSERT INTO @tempTableVar SELECT TOP 1 ... --bunch of fields from temp table FROM #tempTable WHERE IsProcessed = 0 -- check for first unprocessed record --The following variables need to be initiated so that it can be used for executing --The main stored procedure that popuplates the marketing tables. SELECT ... --bunch of variables initiated from the temp table variable FROM @tempTableVar BEGIN TRY BEGIN TRANSACTION IF (SELECT COUNT(*) FROM @tempTableVar) = 0 BEGIN BREAK; ROLLBACK TRANSACTION; END --Another stored procedure is called here to be executed with the variables that --were initiated a bit further up, as input parameters. EXEC [SomeDatabase].[Schema].[SomeOtherStoredProcedure] ... --bunch of input parameters / variables COMMIT TRANSACTION END TRY BEGIN CATCH IF (XACT_STATE() &lt;&gt; 0) ROLLBACK TRAN; SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE(), @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'); SELECT @ErrorMessage = ERROR_MESSAGE() + '(Error %d, Level %d, State %d, Procedure %s, Line %d).' --Errors on this level, essentially raised by the secondary stored procedure, are --stored in an error logging table for later reference INSERT INTO [schema].[ImportErrors] ( ErrorType, ErrorMessaae) VALUES ( 'Some Type', @ErrorMessage); UPDATE #tempTable SET IsProcessed = 1 WHERE someField = @someVariable ... DELETE FROM @tempTableVar END CATCH --Update the temp table's IsProcessed field to 1 for the affected record, if there --are no errors. UPDATE #tempTable SET IsProcessed = 1 WHERE someField = @someVariable ... --Delete the entry from the temp table variable so that it can be used for the next --record. DELETE FROM @tempTableVar END IF(SELECT OBJECT_ID('tempdb..#tempTable')) IS NOT NULL BEGIN DROP TABLE #tempTable END SET NOCOUNT OFF SET XACT_ABORT OFF END TRY BEGIN CATCH IF (XACT_STATE() &lt;&gt; 0) ROLLBACK TRAN; -- Do the error handling. -- Assign variables to error-handling functions that -- capture information for RAISERROR. SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE(), @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-'); -- Building the message string that will contain original -- error information. SELECT @ErrorMessage = 'Message: '+ ERROR_MESSAGE() + N' (Error %d, Level %d, State %d, Procedure %s, Line %d).'; --SELECT @ErrorMessage = @ErrorMessage + @CustomerCategoryID + ' ' + @CustomerID + ' ' + @EmailAddress -- Raise an error: msg_str parameter of RAISERROR will contain -- the original error information. RAISERROR ( @ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, -- parameter: original error number. @ErrorSeverity, -- parameter: original error severity. @ErrorState, -- parameter: original error state. @ErrorProcedure, -- parameter: original error procedure name. @ErrorLine); -- parameter: original error line number. END CATCH </code></pre> <p>Can someone please tell me what I'm doing wrong?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USDebbie Dippenaar
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COI'm assuming this is SQL Server? Please always include the tags for the specific RDBMS (and version) if appropriate as well as the more generic SQL or TSQL / PLSQL etc; there is almost always something DBMS specific that can help.
      singulars
      1. POSQL transactions / error handling / updates
      1. USBen
    2. COSecondly, that's rather a lot of code. Is there anyway you can condense it to something shorter and more understandable that equally highlights your problem. In doing so you might be able to solve the problem yourself; it also means that people are more likely to be able to help and it makes it easier for them do so.
      singulars
      1. POSQL transactions / error handling / updates
      1. USBen
    3. CONot acccepting answers?
      singulars
      1. POSQL transactions / error handling / updates
      1. USpodiluska
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload