SQL Server Exception Handling Part 13

 in SQL Server  tagged error / exception / SQL Server Exceptions by
This entry is part 14 of 15 in the series SQL Server Exceptions

SQL Server Exceptions

This post is a continuation of our SQL Server project that uses exception handling in our code to import a text file that is not a delimited file.

In our example project, below is what our input file looks like. ABC Inc. sends us files periodically that have this format. Each time we get this file we need to run a stored procedure, called [AllProcedures] that will import this file into a table in SQL Server called [dbo].[Reports]. We expect the file to be in the same format each time we receive one. We also expect that the four-digit code number is different each time. For this example, we also expect that the file has exactly nine rows. In the real world that number would likely be higher, allowing for more transaction lines. In the real world there would also likely be more information in the file such as the date and more information for each transaction. However, for now, we want to focus on the process and not make things too detailed so as to cloud the important parts of the exercise. In the real world we would use more descriptive names for our tables and stored procedures that we are using here.

Full Listing

This post includes a full SQL Server query listing that will create a new database, tables and stored procedures that allow you to test out this code for yourself. Feel free to make changes to the code and use it freely in your own projects. Part 9 also has a full listing like this one, but that is based on a previous version of the program, I encourage you to use this updated version.

Input Text File (C:\incoming\rpt2.txt)

view source
print?
1Report Header from ABC Inc.
2 
3File name: 123.txt
4Source: ABC Inc.
5Four-digit code number: 5578
6Transaction number: 12345   Transaction Amount:    12000
7Transaction number: 12346   Transaction Amount:   770000
8 
9End of Report

SQL Table after importing (dbo.Reports)

Below is a screen shot of the above file’s data after it has been imported into the table dbo.Reports using the sql listing provided below.

Here is a screenshot from SQL Server Management Studio (SSMS).

It shows the database and the table objects and the stored procedures that the below sql script will create for you. As a use of the program you will need to first create the input file in the directory C:\incoming\rpt2.txt. You could change the folder name or place the folder deeper in the hierarchy, but you will need to change the code as well.

Copy this code listing into a new query in SSMS. Execute the query with the Execute button. Don’t worry about the dependency message you get. It will still work. Next, Right click the stored procedure called AllProcedures and select Execute Stored Procedure… and then click the OK button. Now right-click the table dbo.Reports and click Select Top 1000 Rows. You should see the critical contents of your input text file listed in the Results pane of SSMS.

Below is the complete sql listing. It creates a new database, creates the necessary tables and the stored procedures as well. If you wish, feel free to copy this entire listing into a new query in SSMS. Execute the query. Run the stored procedure AllProcedures. You should have imported the file you created in the instructions above.

To copy the listing below, click on the icon with the two blue brackets in the upper right corner of the box that contains the listing. In Windows, click Ctrl+A then Ctrl+C to copy the text. Paste that into a new query window in SSMS and then execute the query. Have a look at the Results pane. Right-click the table dbo.Reports and view the rows in the table with Select Top 1000 Rows, or just write your own select query.

Try to import the same file again and see what message you get. You should get an error that says you would have duplicates.

Try changing the one aspect of the contents of the file and run it again. Try changing the code number. Try changing the code number and the amounts. Notice how delicate the program is.

view source
print?
1CREATE DATABASE ImportABCFile
2GO
3USE ImportABCFile
4GO
5CREATE TABLE [dbo].[InReport](
6    [InRows] [varchar](400) NULL
7) ON [PRIMARY]
8 
9GO
10CREATE TABLE [dbo].[InReportHistory](
11    [Id] [int] IDENTITY(1,1) NOT NULL,
12    [SystemDateTime] [datetime] NOT NULL,
13    [InRows] [varchar](400) NOT NULL
14) ON [PRIMARY]
15 
16GO
17CREATE TABLE [dbo].[Reports](
18    [FullFileName] [varchar](50) NULL,
19    [SourceCompany] [varchar](50) NULL,
20    [CodeNumber] [varchar](4) NULL,
21    [TransNumber] [varchar](5) NULL,
22    [TransAmount] [varchar](8) NULL
23) ON [PRIMARY]
24 
25GO
26-- ====================================================================================
27CREATE PROCEDURE [dbo].[BulkInsertFile]
28AS
29    BEGIN
30        -- SET NOCOUNT ON added to prevent extra result sets from
31        -- interfering with SELECT statements.
32    SET NOCOUNT ON;
33    -- Detect whether the procedure was called from an active transaction and save
34    -- that for later use. In the procedure, @TranCounter = 0 
35    -- means there was no active transaction and the procedure started one.
36    -- @TranCounter > 0 means an active transaction was started before the
37    -- procedure was called. 
38    DECLARE @TranCountAtStart INT; 
39    DECLARE @rowsread INT;
40    DECLARE @rowsexpected INT;  -- number of rows in the input file
41    SET @rowsexpected = 9;
42    SET @TranCountAtStart = @@TRANCOUNT; 
43    IF @TranCountAtStart > 0
44        BEGIN 
45            -- Procedure called when there is an active transaction.
46            -- Create a savepoint to be able to roll back only the work done 
47            -- in the procedure if there is an error.
48            SAVE TRANSACTION ProcedureSave;
49        END
50    ELSE 
51        BEGIN
52            -- Procedure must start its own transaction.
53            BEGIN TRANSACTION;
54        END
55    -- 
56    BEGIN TRY
57        -- Here is where you do your work
58        BULK INSERT [dbo].[InReport]
59        FROM 'C:\incoming\rpt2.txt'
60        WITH
61        (
62        DATAFILETYPE = 'char',
63        --FIELDTERMINATOR = ',',  -- we do not need this setting
64        ROWTERMINATOR = '0x0a',
65        FIRSTROW = 1   -- Start at row 1
66        )
67        -- What if the file rpt2.txt is completely empty?
68        -- What if the report text is only partially there? There should be
69        -- nine (9) rows affected.
70        -- Anything other than 9 rows imported consitutes a critical error
71        -- and we need to roll back the transaction
72        SET @rowsread = @@ROWCOUNT;
73        IF @rowsread <> @rowsexpected
74        BEGIN
75            DECLARE @errmsg AS VARCHAR(200);
76            SET @errmsg = 'In SProc [BulkInsertFile]: Error. Expecting ' + cast(@rowsexpected as varchar(5)) + ' rows, but ' + cast(@rowsread as varchar(5)) + ' rows were found.';
77            RAISERROR(@errmsg, 16,1);
78        END
79        ELSE
80            BEGIN
81                SET @errmsg = 'In SProc [BulkInsertFile]: Read ' + cast(@rowsread as varchar(5)) + ' rows into [dbo].[InReport]';
82                RAISERROR(@errmsg, 10,1);
83            END
84        -- End of our work.
85 
86       IF @TranCountAtStart = 0 
87            -- @TranCountAtStart = 0 means no transaction was started before the procedure was called.
88            -- The procedure must commit the transaction it started.
89            COMMIT TRANSACTION; 
90    END TRY
91    -- ------------------------------------------------------------------------------------------ 
92    BEGIN CATCH
93        -- An error occurred; must determine which type of rollback will 
94        -- roll back only the work done in the procedure.
95        IF @TranCountAtStart = 0
96            BEGIN 
97                -- Transaction started in procedure. Roll back complete transaction.
98                ROLLBACK TRANSACTION;
99                PRINT 'In SProc [BulkInsertFile],  *** Rolling Back Transaction ***';
100            END 
101        ELSE 
102            -- Transaction started before procedure called, do not roll back modifications
103            -- made before the procedure was called. 
104            IF XACT_STATE() <> -1
105                BEGIN 
106                    -- If the transaction is still valid, just roll back to the savepoint set at the
107                    -- start of the stored procedure. 
108                    ROLLBACK TRANSACTION ProcedureSave; 
109                    -- If the transaction is uncommitable, a rollback to the save point is not allowed
110                    -- because the save point rollback writes to the log. Just return to the caller, which
111                    -- should roll back the outer transaction.
112                    PRINT 'In SProc [BulkInsertFile],  *** Rolling Back Procedure ***'; 
113                END
114 
115        -- After the appropriate rollback, echo error information to the caller.
116        DECLARE @ErrorMessage NVARCHAR(4000); 
117        DECLARE @ErrorSeverity INT; 
118        DECLARE @ErrorState INT; 
119   
120        SELECT @ErrorMessage = ERROR_MESSAGE(); 
121        SELECT @ErrorSeverity = ERROR_SEVERITY(); 
122        SELECT @ErrorState = ERROR_STATE(); 
123   
124        RAISERROR (@ErrorMessage, -- Message text. 
125                   @ErrorSeverity, -- Severity. 
126                   @ErrorState -- State. 
127                   ); 
128    END CATCH 
129END
130 
131GO
132-- ==================================================================================
133CREATE PROCEDURE [dbo].[CopyInRepToInRepHist]
134AS
135BEGIN
136    -- SET NOCOUNT ON added to prevent extra result sets from
137    -- interfering with SELECT statements.
138    SET NOCOUNT ON;
139 
140    -- Detect whether the procedure was called from an active transaction and save
141    -- that for later use. In the procedure, @TranCounter = 0 
142    -- means there was no active transaction and the procedure started one.
143    -- @TranCounter > 0 means an active transaction was started before the
144    -- procedure was called. 
145    DECLARE @TranCountAtStart INT; 
146    SET @TranCountAtStart = @@TRANCOUNT; 
147    IF @TranCountAtStart > 0
148        BEGIN 
149            -- Procedure called when there is an active transaction.
150            -- Create a savepoint to be able to roll back only the work done 
151            -- in the procedure if there is an error.
152            SAVE TRANSACTION ProcedureSave; 
153        END
154    ELSE 
155        BEGIN
156            -- Procedure must start its own transaction.
157            BEGIN TRANSACTION;
158        END
159    -- 
160    BEGIN TRY
161        -- Here is where we do your work
162 
163        -- The columns of table dbo.InReportHistory are:
164        -- Id, SystemDateTime, InRow
165        -- Id is an Identity and will automatically increment by 1.
166        DECLARE @dt as datetime
167        SET @dt = GETDATE()
168 
169        INSERT INTO dbo.InReportHistory
170        (SystemDateTime, InRows)
171        SELECT @dt, irep.InRows
172        FROM dbo.InReport AS irep;
173 
174        -- End of our work
175 
176        -- Get here if no errors; must commit any transaction started in the
177        -- procedure, but not commit a transaction started before the transaction was called.
178        IF @TranCountAtStart = 0 
179            -- @TranCountAtStart = 0 means no transaction was started before the procedure was called.
180            -- The procedure must commit the transaction it started.
181            COMMIT TRANSACTION; 
182    END TRY
183    -- ------------------------------------------------------------------------------------------ 
184    BEGIN CATCH
185        -- An error occurred; must determine which type of rollback will 
186        -- roll back only the work done in the procedure.
187        IF @TranCountAtStart = 0
188            BEGIN 
189                -- Transaction started in procedure. Roll back complete transaction.
190                ROLLBACK TRANSACTION;
191                PRINT 'In SProc [CopyInRepToInRepHist],  *** Rolling Back Transaction ***';
192            END 
193        ELSE 
194            -- Transaction started before procedure called, do not roll back modifications
195            -- made before the procedure was called. 
196            IF XACT_STATE() <> -1
197                BEGIN 
198                    -- If the transaction is still valid, just roll back to the savepoint set at the
199                    -- start of the stored procedure. 
200                    ROLLBACK TRANSACTION ProcedureSave; 
201                    -- If the transaction is uncommitable, a rollback to the save point is not allowed
202                    -- because the save point rollback writes to the log. Just return to the caller, which
203                    -- should roll back the outer transaction.
204                    PRINT 'In SProc [CopyInRepToInRepHist],  *** Rolling Back Procedure ***'; 
205                END
206 
207        -- After the appropriate rollback, echo error information to the caller.
208        DECLARE @ErrorMessage NVARCHAR(4000); 
209        DECLARE @ErrorSeverity INT; 
210        DECLARE @ErrorState INT; 
211   
212        SELECT @ErrorMessage = ERROR_MESSAGE(); 
213        SELECT @ErrorSeverity = ERROR_SEVERITY(); 
214        SELECT @ErrorState = ERROR_STATE(); 
215   
216        RAISERROR (@ErrorMessage, -- Message text. 
217                   @ErrorSeverity, -- Severity. 
218                   @ErrorState -- State. 
219                   ); 
220    END CATCH
221END
222 
223GO
224-- Create Procedures ====================================================================
225CREATE PROCEDURE [dbo].[DeleteRowsInReport]
226AS
227BEGIN
228    -- SET NOCOUNT ON added to prevent extra result sets from
229    -- interfering with SELECT statements.
230    SET NOCOUNT ON;
231        -- Detect whether the procedure was called from an active transaction and save
232    -- that for later use. In the procedure, @TranCounter = 0 
233    -- means there was no active transaction and the procedure started one.
234    -- @TranCounter > 0 means an active transaction was started before the
235    -- procedure was called. 
236    DECLARE @TranCountAtStart INT; 
237    SET @TranCountAtStart = @@TRANCOUNT; 
238    IF @TranCountAtStart > 0
239        BEGIN 
240            -- Procedure called when there is an active transaction.
241            -- Create a savepoint to be able to roll back only the work done 
242            -- in the procedure if there is an error.
243            SAVE TRANSACTION ProcedureSave; 
244        END
245    ELSE 
246        BEGIN
247            -- Procedure must start its own transaction.
248            BEGIN TRANSACTION;
249        END
250    -- 
251    BEGIN TRY
252        -- Here is where we do your work.
253        DECLARE @rowsdeleted AS INT;
254        DELETE FROM dbo.InReport
255        SET @rowsdeleted = @@rowcount;
256        IF @rowsdeleted = 0
257        BEGIN
258            -- raise an error. Non-critical. Informational only. Do not jump to CATCH.
259            DECLARE @errMsg AS VARCHAR(100)
260            SET @errMsg = 'No records deleted from table dbo.InReport because table was empty.';
261            RAISERROR(@errMsg, 10,1) WITH NOWAIT;
262            -- Remember: When RAISERROR is run with a severity of 11 or higher in a TRY block
263            -- it transfers control to the associated CATCH block.
264        END
265        -- End of our work.    
266         
267        -- Get here if no errors; must commit any transaction started in the
268        -- procedure, but not commit a transaction started before the transaction was called.
269        IF @TranCountAtStart = 0 
270            -- @TranCountAtStart = 0 means no transaction was started before the procedure was called.
271            -- The procedure must commit the transaction it started.
272            COMMIT TRANSACTION; 
273    END TRY
274    -- ------------------------------------------------------------------------------------------ 
275    BEGIN CATCH
276        -- An error occurred; must determine which type of rollback will 
277        -- roll back only the work done in the procedure.
278        IF @TranCountAtStart = 0
279            BEGIN 
280                -- Transaction started in procedure. Roll back complete transaction.
281                ROLLBACK TRANSACTION;
282                PRINT 'In SProc [DeleteRowsInReport],  *** Rolling Back Transaction ***';
283            END 
284        ELSE 
285            -- Transaction started before procedure called, do not roll back modifications
286            -- made before the procedure was called. 
287            IF XACT_STATE() <> -1
288                BEGIN 
289                    -- If the transaction is still valid, just roll back to the savepoint set at the
290                    -- start of the stored procedure. 
291                    ROLLBACK TRANSACTION ProcedureSave; 
292                    -- If the transaction is uncommitable, a rollback to the save point is not allowed
293                    -- because the save point rollback writes to the log. Just return to the caller, which
294                    -- should roll back the outer transaction.
295                    PRINT 'In SProc [DeleteRowsInReport],  *** Rolling Back Procedure ***'; 
296                END
297 
298        -- After the appropriate rollback, echo error information to the caller.
299        DECLARE @ErrorMessage NVARCHAR(4000); 
300        DECLARE @ErrorSeverity INT; 
301        DECLARE @ErrorState INT; 
302   
303        SELECT @ErrorMessage = ERROR_MESSAGE(); 
304        SELECT @ErrorSeverity = ERROR_SEVERITY(); 
305        SELECT @ErrorState = ERROR_STATE(); 
306   
307        RAISERROR (@ErrorMessage, -- Message text. 
308                   @ErrorSeverity, -- Severity. 
309                   @ErrorState -- State. 
310                   ); 
311    END CATCH 
312END
313GO
314-- ====================================================================================
315CREATE PROCEDURE [dbo].[FromInReportToReports]
316AS
317BEGIN
318    -- SET NOCOUNT ON added to prevent extra result sets from
319    -- interfering with SELECT statements.
320    SET NOCOUNT ON;
321    -- Detect whether the procedure was called from an active transaction and save
322    -- that for later use. In the procedure, @TranCounter = 0 
323    -- means there was no active transaction and the procedure started one.
324    -- @TranCounter > 0 means an active transaction was started before the
325    -- procedure was called. 
326    DECLARE @TranCountAtStart INT; 
327    SET @TranCountAtStart = @@TRANCOUNT; 
328    IF @TranCountAtStart > 0
329        BEGIN 
330            -- Procedure called when there is an active transaction.
331            -- Create a savepoint to be able to roll back only the work done 
332            -- in the procedure if there is an error.
333            SAVE TRANSACTION ProcedureSave;
334        END
335    ELSE 
336        BEGIN
337            -- Procedure must start its own transaction.
338            BEGIN TRANSACTION;
339        END
340    -- 
341    BEGIN TRY
342        -- Here is where we do your work ------------------------------------------------------
343        DECLARE @rowtext VARCHAR(1000)
344        DECLARE @filename VARCHAR(50)
345        DECLARE @source VARCHAR(50)
346        DECLARE @codenumber VARCHAR(4)
347        DECLARE @transnumber VARCHAR(10)
348        DECLARE @transamount VARCHAR(19)
349        DECLARE @numberdups INT
350        DECLARE @errmsg VARCHAR(100)
351 
352        SET @rowtext = (SELECT InRows FROM dbo.InReport WHERE InRows LIKE '%File name:%');
353        SET @filename = RTRIM(SUBSTRING(@rowtext,12,50))
354        IF LEN(@filename) = 0
355            BEGIN
356                SET @errmsg = 'No File Name found. Terminating and rolling back.'
357                RAISERROR(@errmsg, 16, 1) -- will roll back
358            END
359        --
360        SET @rowtext = (SELECT InRows FROM dbo.InReport WHERE InRows LIKE '%Source:%');
361        SET @source = RTRIM(SUBSTRING(@rowtext,9,50))
362        IF LEN(@source) = 0
363            BEGIN
364                SET @errmsg = 'No Source found. Terminating and rolling back.'
365                RAISERROR(@errmsg, 16, 1) -- will roll back
366            END
367        --
368        SET @rowtext = (SELECT InRows FROM dbo.InReport WHERE InRows LIKE '%code number:%');
369        SET @codenumber = SUBSTRING(@rowtext,25,4)
370        IF LEN(@codenumber) = 0
371            BEGIN
372                SET @errmsg = 'No Code Number found. Terminating and rolling back.'
373                RAISERROR(@errmsg, 16, 1) -- will roll back
374            END
375        --
376        -- Each company that gives us a file uses a unique code number to identify
377        -- each report. Therefore if we find that we already have these values in
378        -- our dbo.InReports table, raise a critical error because we must be running
379        -- this program a second time for the same file, and we do not want duplicates
380        -- in our dbo.Reports table. To this end we will call a user-defined function
381        DECLARE @duplic INT -- Declare the variable to receive the output value of the procedure.
382        EXEC dbo.NumberOfDuplicates @source, @codenumber, @numDupReturned = @duplic OUTPUT
383        IF @duplic = 0
384            BEGIN
385                SET @errmsg = 'No duplicates will exist after adding this new file.'
386                RAISERROR(@errmsg, 10, 1) -- Informational message only. No error. Carry on.
387            END
388        ELSE
389            BEGIN
390                SET @errmsg = 'Has this file already been processed?'
391                RAISERROR(@errmsg, 16, 1) -- Error.
392            END
393        --
394        -- Let's work on the detail lines, now that we have finished with the header lines.
395        DECLARE @rowsread AS INT;
396        SELECT * INTO #temptbl
397        FROM dbo.InReport
398        WHERE InRows LIKE '%Transaction number:%'
399        -- if no rows were added to temp table, then no transaction numbers found
400        -- and that is a critical error, therefore raiserror and roll back
401        SET @rowsread = @@ROWCOUNT;
402        IF @rowsread = 0
403            BEGIN
404                SET @errmsg = '0 transaction numbers found. Terminating and rolling back.'
405                RAISERROR(@errmsg, 16, 1) -- will roll back
406            END
407        ELSE
408            BEGIN
409                SET @errmsg = CAST(@rowsread AS VARCHAR(5)) + ' transaction numbers found.'
410                RAISERROR(@errmsg, 10, 1) -- Informational message only. No error. Carry on.
411 
412                WHILE (SELECT COUNT(*) FROM #temptbl) > 0
413                    BEGIN
414                    SELECT TOP 1 @rowtext = InRows FROM #temptbl
415                    SET @transnumber = SUBSTRING(@rowtext,21,5)
416                    SET @transamount = LTRIM(SUBSTRING(@rowtext,49,8))
417                    INSERT INTO dbo.Reports (FullFileName, SourceCompany, CodeNumber, TransNumber, TransAmount)
418                    VALUES (@filename, @source, @codenumber, @transnumber, @transamount);
419                    DELETE #temptbl Where InRows = @rowtext
420                    END
421            END
422        /*
423        PRINT 'To debug, force divide by zero error here in SProc [FromInReportToReports]';
424        DECLARE @bad as int;
425        SET @bad = 5/0;
426        */
427 
428        -- End of our work ---------------------------------------------------------------------
429 
430        -- Get here if no errors; must commit any transaction started in the
431        -- procedure, but not commit a transaction started before the transaction was called.
432        IF @TranCountAtStart = 0 
433            -- @TranCountAtStart = 0 means no transaction was started before the procedure was called.
434            -- The procedure must commit the transaction it started.
435            COMMIT TRANSACTION; 
436    END TRY
437    -- ------------------------------------------------------------------------------------------ 
438    BEGIN CATCH
439        -- An error occurred; must determine which type of rollback will 
440        -- roll back only the work done in the procedure.
441        IF @TranCountAtStart = 0
442            BEGIN 
443                -- Transaction started in procedure. Roll back complete transaction.
444                ROLLBACK TRANSACTION;
445                PRINT 'In SProc [FromInReportToReports],  *** Rolling Back Transaction ***';
446            END 
447        ELSE 
448            -- Transaction started before procedure called, do not roll back modifications
449            -- made before the procedure was called. 
450            IF XACT_STATE() <> -1
451                BEGIN 
452                    -- If the transaction is still valid, just roll back to the savepoint set at the
453                    -- start of the stored procedure. 
454                    ROLLBACK TRANSACTION ProcedureSave; 
455                    -- If the transaction is uncommitable, a rollback to the save point is not allowed
456                    -- because the save point rollback writes to the log. Just return to the caller, which
457                    -- should roll back the outer transaction.
458                    PRINT 'In SProc [FromInReportToReports],  *** Rolling Back Procedure ***'; 
459                END
460 
461        -- After the appropriate rollback, echo error information to the caller.
462        DECLARE @ErrorMessage NVARCHAR(4000); 
463        DECLARE @ErrorSeverity INT; 
464        DECLARE @ErrorState INT; 
465   
466        SELECT @ErrorMessage = ERROR_MESSAGE(); 
467        SELECT @ErrorSeverity = ERROR_SEVERITY(); 
468        SELECT @ErrorState = ERROR_STATE(); 
469   
470        RAISERROR (@ErrorMessage, -- Message text. 
471                   @ErrorSeverity, -- Severity. 
472                   @ErrorState -- State. 
473                   ); 
474    END CATCH
475END
476GO
477 
478-- =============================================================================
479-- Author:      Mike
480-- Create date: March 23, 2017
481-- Description: Check for duplicates in the table [dbo.Reports].
482--              Return the number of duplicates to the calling routine as an INT.
483--              If no duplcates are found, return a zero.
484--              The duplicates we are looking for do not already exist in the
485--              table dbo.Reports; what consititues a duplicate is if a row is
486--              added to dbo.Reports that has the two pieces of data in them
487--              that are stored in our two input parameters, a duplicate in the
488--              table would exist.
489--              Here we raise our own error if there would be duplicates, and the
490--              @numDupReturned will never really be returned unless there are
491--              no duplicates (@numDupReturned = 0).
492-- =============================================================================
493CREATE PROCEDURE [dbo].[NumberOfDuplicates]
494(
495@source VARCHAR(100),
496@codenumber VARCHAR(4),
497@numDupReturned INT OUTPUT
498) 
499AS
500BEGIN
501    -- SET NOCOUNT ON added to prevent extra result sets from
502    -- interfering with SELECT statements.
503    SET NOCOUNT ON;
504     -- Detect whether the procedure was called from an active transaction and save
505    -- that for later use. In the procedure, @TranCounter = 0 
506    -- means there was no active transaction and the procedure started one.
507    -- @TranCounter > 0 means an active transaction was started before the
508    -- procedure was called. 
509    DECLARE @TranCountAtStart INT; 
510    SET @TranCountAtStart = @@TRANCOUNT; 
511    IF @TranCountAtStart > 0
512        BEGIN 
513            -- Procedure called when there is an active transaction.
514            -- Create a savepoint to be able to roll back only the work done 
515            -- in the procedure if there is an error.
516            SAVE TRANSACTION ProcedureSave; 
517        END
518    ELSE 
519        BEGIN
520            -- Procedure must start its own transaction.
521            BEGIN TRANSACTION;
522        END
523    -- 
524    BEGIN TRY
525        DECLARE @numDup INT
526        DECLARE @errmsg VARCHAR(300)
527        /* Example of DYNAMIC QUERY
528        -- DECLARE @numDupOUT INT
529        -- DECLARE @src VARCHAR(100)
530        -- DECLARE @codenum VARCHAR(4)
531        -- DECLARE @sqlstring VARCHAR(200)
532        -- DECLARE @ParmDefinition NVARCHAR(300)
533        --SET @sqlstring = N'SELECT @numDupOUT = COUNT(*) FROM [ImportFileDemo].[dbo].[Reports]'
534        --SET @sqlstring = @sqlstring + N'WHERE [SourceCompany] LIKE ''%'
535        --SET @sqlstring = @sqlstring + @src
536        --SET @sqlstring = @sqlstring + N'%'' AND [CodeNumber] LIKE '
537        --SET @sqlstring = @sqlstring + @codenum
538        --SET @sqlstring = @sqlstring + N''''  -- you escape a single quote by doubling it up
539        --SET @ParmDefinition = N'@src VARCHAR(100), @codenum VARCHAR(4), @numDupOUT INT OUTPUT';
540        -- EXECUTE sp_executesql @sqlstring, @ParmDefinition, @src = @source, @codenum = @codenumber, @numDupOUT = @numDup OUTPUT;
541        --SET @numDupReturned = @numDup    
542        */
543        SELECT @numDup = COUNT(*)
544            FROM dbo.Reports
545            WHERE  @source = SourceCompany AND @codenumber = CodeNumber;
546        SET @numDupReturned = @numDup;
547        IF @numDup > 0
548        BEGIN
549            SET @errmsg = 'Procedure [NumberOfDuplicates]: Importing this file will cause duplicates in dbo.Reports. File not imported.'
550            RAISERROR(@errmsg, 16, 1) -- will roll back
551        END
552    END TRY
553    BEGIN CATCH
554        -- An error occurred; must determine which type of rollback will 
555        -- roll back only the work done in the procedure.
556        IF @TranCountAtStart = 0
557            BEGIN 
558                -- Transaction started in procedure. Roll back complete transaction.
559                ROLLBACK TRANSACTION;
560                PRINT 'In SProc [NumberOfDuplicates],  *** Rolling Back Transaction ***';
561            END 
562        ELSE 
563            -- Transaction started before procedure called, do not roll back modifications
564            -- made before the procedure was called. 
565            IF XACT_STATE() <> -1
566                BEGIN 
567                    -- If the transaction is still valid, just roll back to the savepoint set at the
568                    -- start of the stored procedure. 
569                    ROLLBACK TRANSACTION ProcedureSave; 
570                    -- If the transaction is uncommitable, a rollback to the save point is not allowed
571                    -- because the save point rollback writes to the log. Just return to the caller, which
572                    -- should roll back the outer transaction.
573                    PRINT 'In SProc [NumberOfDuplicates], *** Rolling Back Procedure ***'; 
574                END
575 
576        -- After the appropriate rollback, echo error information to the caller.
577        DECLARE @ErrorMessage NVARCHAR(4000); 
578        DECLARE @ErrorSeverity INT; 
579        DECLARE @ErrorState INT; 
580   
581        SELECT @ErrorMessage = ERROR_MESSAGE(); 
582        SELECT @ErrorSeverity = ERROR_SEVERITY(); 
583        SELECT @ErrorState = ERROR_STATE(); 
584   
585        RAISERROR (@ErrorMessage, -- Message text. 
586                   @ErrorSeverity, -- Severity. 
587                   @ErrorState -- State. 
588                   ); 
589    END CATCH
590END
591GO
592-- ================================================================================
593CREATE PROCEDURE [dbo].[AllProcedures]
594AS
595    -- Detect whether the procedure was called from an active transaction and save
596    -- that for later use. In the procedure, @TranCounter = 0 
597    -- means there was no active transaction and the procedure started one.
598    -- @TranCounter > 0 means an active transaction was started before the
599    -- procedure was called. 
600    DECLARE @TranCountAtStart INT; 
601    SET @TranCountAtStart = @@TRANCOUNT; 
602    IF @TranCountAtStart > 0
603        BEGIN 
604            -- Procedure called when there is an active transaction.
605            -- Create a savepoint to be able to roll back only the work done 
606            -- in the procedure if there is an error.
607            SAVE TRANSACTION ProcedureSave;
608        END
609    ELSE 
610        BEGIN
611            -- Procedure must start its own transaction.
612            BEGIN TRANSACTION;
613        END
614    -- 
615    BEGIN TRY
616        -- Here is where we do your work -------------------------------------------------------
617        -- PRINT 'Debugging message - In SProc [AllProcedures], Now inside the BEGIN TRY block.'
618        EXEC dbo.DeleteRowsInReport  -- delete rows in table InReport
619        EXEC dbo.BulkInsertFile -- import from file to table InReport (one column: InRows)
620        EXEC dbo.CopyInRepToInRepHist -- copy to provide an audit trail
621        EXEC dbo.FromInReportToReports -- extract data into table dbo.Reports
622        -- End of our work -----------------------------------------------------------------------
623 
624        -- Get here if no errors; must commit any transaction started in the
625        -- procedure, but not commit a transaction started before the transaction was called.
626        IF @TranCountAtStart = 0 
627            -- @TranCountAtStart = 0 means no transaction was started before the procedure was called.
628            -- The procedure must commit the transaction it started.
629            COMMIT TRANSACTION;
630        --
631        SELECT [FullFileName]
632                ,[SourceCompany]
633                ,[CodeNumber]
634                ,[TransNumber]
635                ,[TransAmount]
636            FROM [ImportFileDemo].[dbo].[Reports]
637         
638    END TRY
639    -- ------------------------------------------------------------------------------------------ 
640    BEGIN CATCH
641        -- An error occurred; must determine which type of rollback will 
642        -- roll back only the work done in the procedure.
643        IF @TranCountAtStart = 0
644            BEGIN 
645                -- Transaction started in procedure. Roll back complete transaction.
646                ROLLBACK TRANSACTION;
647                PRINT 'In SProc [AllProcedures],  *** Rolling Back Transaction ***';
648            END 
649        ELSE 
650            -- Transaction started before procedure called, do not roll back modifications
651            -- made before the procedure was called. 
652            IF XACT_STATE() <> -1
653                BEGIN 
654                    -- If the transaction is still valid, just roll back to the savepoint set at the
655                    -- start of the stored procedure. 
656                    ROLLBACK TRANSACTION ProcedureSave; 
657                    -- If the transaction is uncommitable, a rollback to the save point is not allowed
658                    -- because the save point rollback writes to the log. Just return to the caller, which
659                    -- should roll back the outer transaction.
660                    PRINT ' In SProc [AllProcedures], *** Rolling Back Procedure ***'; 
661                END
662 
663        -- After the appropriate rollback, echo error information to the caller.
664        DECLARE @ErrorMessage NVARCHAR(4000); 
665        DECLARE @ErrorSeverity INT; 
666        DECLARE @ErrorState INT; 
667   
668        SELECT @ErrorMessage = ERROR_MESSAGE(); 
669        SELECT @ErrorSeverity = ERROR_SEVERITY(); 
670        SELECT @ErrorState = ERROR_STATE(); 
671   
672        RAISERROR (@ErrorMessage, -- Message text. 
673                   @ErrorSeverity, -- Severity. 
674                   @ErrorState -- State. 
675                   ); 
676    END CATCH 
677GO
678 
679CREATE PROCEDURE [dbo].[xDropandCreateAllTables]
680    -- Use this one for debugging purposes.
681AS
682BEGIN
683    -- SET NOCOUNT ON added to prevent extra result sets from
684    -- interfering with SELECT statements.
685    SET NOCOUNT ON;
686 
687    DROP TABLE [dbo].[InReport]
688    CREATE TABLE [dbo].[InReport](
689        [InRows] [varchar](400) NULL
690    ) ON [PRIMARY]
691 
692 
693    DROP TABLE [dbo].[InReportHistory]
694    CREATE TABLE [dbo].[InReportHistory](
695        [Id] [int] IDENTITY(1,1) NOT NULL,
696        [SystemDateTime] [datetime] NOT NULL,
697        [InRows] [varchar](400) NOT NULL
698    ) ON [PRIMARY]
699 
700    DROP TABLE [dbo].[Reports]
701    CREATE TABLE [dbo].[Reports](
702        [FullFileName] [varchar](50) NULL,
703        [SourceCompany] [varchar](50) NULL,
704        [CodeNumber] [varchar](4) NULL,
705        [TransNumber] [varchar](5) NULL,
706        [TransAmount] [varchar](8) NULL
707    ) ON [PRIMARY]
708END
709GO

Current Procedure

One improvement to the error trapping code is to use a variable to get the current running stored procedure. The current procedure is stored in @@PROCID. We could use this instead of “hard-coding” the stored procedure name inside of the error message. In this way if we ever need to change the name of the procedure we don’t need to change the error messages. That makes it much easier to maintain the code.

Global Variables

In SQL Server, global variables start with @@. The website Code Project has a very good article on these global variables. Global variables represent a special type of variable. The server always maintain the values of these variables. All the global variables represent information specific to the server or a current user session. You do not need to declare them, since the server constantly maintains them. They are system-defined functions and you cannot declare them.

Series Navigation<< SQL Server Exception Handling Part 12SQL Server Exception Handling Part 14 >>