To handle exceptions in MS SQL, a TRY...CATCH statement works similarly to the exception handling statements that are available from the .NET languages such as C#. A TRY block can be coded around any statement block that can potential raise an error. A TRY block begins with the BEGIN TRY keywords and ends with the END TRY keywords.
BEGIN TRY
{sql_statement|statement_block}
END TRY
BEGIN CATCH
{sql_statement|statement_block}
END CATCH
Following the TRY block, you must code a single CATCH block. A CATCH block begins with the BEGIN CATCH keywords and ends with the END CATCH keywords. Within the CATCH block, you can include any statements that handle the error that might be raised in the TRY block.
BEGIN TRY INSERT INTO employees VALUES ('Clint', 'MacDonald', 'clint.macdonald@email.com', '905-555-1212', CONVERT(datetime, '05-29-21', 1), 'IT Manager', 8000, 5); -- note that departmentID 5 does not exists and will raise a referential integrity error PRINT 'SUCCESS: Record was inserted.'; END TRY BEGIN CATCH PRINT 'FAILURE: Record was not inserted.'; PRINT 'Error ' + CONVERT(varchar, ERROR_NUMBER(), 1) + ': ' + ERROR_MESSAGE(); END CATCH; The message that’s displayed FAILURE: Record was not inserted. Error 547: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Employees_Departments". The conflict occurred in database "dbs710Sample", table "dbo.employees", column 'departmentID'.
The first statement in the CATCH block uses a PRINT statement to display a message that indicates that the INSERT statement in the TRY block failed. The second PRINT statement uses two system functions that are designed to work within a CATCH block to provide more detailed information about the error.
It’s common to use a CATCH block to display information to the user. You can also use a CATCH block to complete other exception handling tasks such as logging information about the error to a log table or completing a rollback within a transaction.
In example 1 above, the INSERT statement provides a department ID that does not exist in the departments table, and therefore referential integrity will throw an error. Then, the script execution will skip the PRINT statement that follows the INSERT statement and resume in the CATCH block. This causes the message that’s shown in this block to be displayed. However, if the INSERT statement had executed successfully, the TRY block would have continued by executing the PRINT statement immediately following the INSERT statement and skipping the CATCH block.
When coding TRY...CATCH statements, you may find that some types of errors aren’t handled. In particular, errors with a low severity are considered warnings and aren’t handled. Conversely, errors with a high severity often cause the database connection to be closed, which prevents them from being handled. Another thing to keep in mind when coding TRY...CATCH statements is that they must be coded within a single batch, stored procedure, or trigger. However, you can nest one TRY...CATCH statement within another. For example, if a CATCH block contains complex code that inserts error data into a log table, you may want to code a TRY...CATCH statement within that CATCH block to catch any errors that might occur there as well.
@@ROWCOUNT |
Returns the number of rows affected by the most recent SQL statement. |
@@ERROR |
Returns the error number generated by the execution of the most recent SQL statement. Returns 0 if no error occurred. |
| Catch Block Only System Functions | |
ERROR_NUMBER() |
Returns the error number. |
ERROR_MESSAGE() |
Returns the error message. |
ERROR_SEVERITY() |
Returns the severity of the error. |
ERROR_STATE() |
Returns the state of the error. |
A sequence is a database object that automatically generates a sequence of integer values. As we have learned before, we can use the IDENTITY attribute for the primary key of a table to generate a simple sequence of numbers that starts with 1 and is incremented by 1. This is a built in simplistic for of a sequence. You wil a sequence object if you want to generate a more complex sequence of numbers or if you want to share the sequence between multiple tables.
An Identity Sequence is a simple sequence that starts at 1 and increments by 1 each time it is referenced. This is typically referred to as an auto-number field and would be quotes as follows:
CREATE TABLE someTable (
pk INT IDENTITY(1,1) PRIMARY KEY,
field2 <field type and size>,
field3 <field type and size>
)
A sequence is created by coding the CREATE SEQUENCE statement followed by the name of the sequence and the starting value. The default increment is 1 unless specified otherwise using the INCREMENT BY clause to increment the sequence numbers by a value other than 1. You can code a negative value to create a descending sequence if that is required.
| Clause | Description |
STARTS WITH |
When the sequence is used for the first time, the first value that will be generated |
INCREMENT BY |
As each new value is generated by the sequence, the difference between the previous and new values is the increment |
MIN VALUE |
The minimum value that will be generated by a sequence. This value typically onlyy applies with decreasing sequences and/or sequences that cycle/wrap and start at the beginning again |
MAX VALUE |
The maximum value that will be generated by a sequence. Once the maximum value is reached no further values will be generated unless the CYCLE option is used. |
CYCLE |
This is a boolean option that determines if the value generation starts over again at the minimum value once it exceeds the maximum value. Be careful using this option with fields being used as a primary key or unique indexes as it would be easy to try and generate duplicate values. |
CACHE |
The cache allows generated numbers to be stored in the cache for a short time. The value associated with the cache simply indicates how many values are stored at any one time. If the size is surpassed, than the oldest one drops off to make room for a new value. |
CREATE SEQUENCE sequence_name [AS integer_type]
[START WITH starting_integer]
[INCREMENT BY increment_integer]
[{MINVALUE minimum_integer | NO MINVALUE}]
[{MAXVALUE maximum_integer | NO MAXVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE cache_size|NOCACHE}]
A statement that creates a sequence that starts with 1
CREATE SEQUENCE TestSequence1
START WITH 1;
A statement that specifies a starting value and an increment for a sequence
CREATE SEQUENCE TestSequence2
START WITH 1001
INCREMENT BY 13;
A statement that specifies all optional parameters for a sequence
CREATE SEQUENCE TestSequence3 AS int
START WITH 100
INCREMENT BY 3
MINVALUE 100
MAXVALUE 1000000
CYCLE CACHE 10;
In order to use a sequence to obtain a value, the key words NEXT VALUE FOR are used. These are mostly going to be used during an INSERT statement, but can be used for many other reasons as well.
Statements that get the next value for a sequence
INSERT INTO SequenceTable
VALUES (NEXT VALUE FOR <sequenceName>, 'First inserted row')
INSERT INTO SequenceTable
VALUES (NEXT VALUE FOR <sequenceName>, 'Second inserted row');
A statement that gets the current value of the sequence
SELECT current_value FROM sys.sequences WHERE name = '<sequenceName>';
The developer must be made aware that when implicit transactions are used, that sequence values may be "reserved" for those pending commits. If a pending transaction is rolled back, the sequence cannot also rollback as there may be other concurrent transactions occurring that have already used. This does result in values in the middle of a sequence being apparently skipped. In more advanced SQL not covered here, the cache can be used to go back and use skipped values, and not interfere with the sequences of concurrent transactions.
As we have learned previously, SQL statements work with an entire result set rather than individual rows. We can create calculated values which are process one row at a time (the Order of Execution), but we can not perform different actions on different rows from a single result set. You may sometimes need to work with the data in a result set one row at a time. To do that, we will use a cursor.
A cursor is similar to an array in compiled programming languages, but with the subtle difference that there are no indexes and the datatype can be a row of data. The cursor allows us to use a WHILE loop to iterate through rows individually to perform row specific tasks.
A cursor is defined using an SQL statement and contains the result set of that SQL statement.
Before you use a cursor to work with individual rows in a result set, you should consider other solutions as standard database access is much faster and uses fewer server resources than cursor-based access.
Declare a cursor
DECLARE cursor_name CURSOR FOR select_statement;
Open the cursor
OPEN cursor_name;
Get column values from the row and store them in a series of variables
FETCH NEXT FROM cursor_name INTO @variable1[, @variable2][, @variable3]...;
Close and deallocate the cursor
CLOSE cursor_name; DEALLOCATE cursor_name;
The @@FETCH_STATUS system function returns 0 if the row was fetched successfully or -1 if the row can’t be fetched because the end of the result set has been reached.
While iterating through the rows a result set, the FETCH NEXT FROM statement is used retrieve the next row and potentially store values in previously declared parameters.
DECLARE
@departmentID INT = 50,
@employeeID INT,
@fullName nvarchar(50),
@salary decimal(8,2),
@commPct decimal(8,2),
@pay decimal(8,2),
@upCount INT = 0;
DECLARE curEmps CURSOR FOR
SELECT
employeeID,
firstName + ' ' + lastName,
monthlySalary,
commissionPercent
FROM employees
WHERE departmentID = @departmentID;
OPEN curEmps;
FETCH NEXT FROM curEmps INTO @employeeID, @fullName, @salary, @commPct;
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @pay = @salary + @salary * IsNull(@commPct,0);
IF @pay > 7000
BEGIN
UPDATE employees SET monthlySalary = @salary * 1.01
WHERE employeeID = @employeeID;
SET @upCount = @upCount + 1;
END;
FETCH NEXT FROM curEmps INTO @employeeID, @fullName, @salary, @commPct;
END;
CLOSE curEmps;
DEALLOCATE curEmps;
PRINT '';
PRINT CONVERT(varchar, @UpCount) + ' row(s) updated.';