Exception Handlers:

In SQL server the errors are always written to sysmessages table, so no exception can be handled here. The type of access to the SQL server from the front end determines the state of the error and how the error information can be retrieved.

Eg.

a).  For ODBC type of access the error messages are stored in the  SQLError object of the SQL server, the front end should query that object to get the error message.

b).  For OLE-DB type of access the error messages are stored in the ISQLServerErrorInfo object. So the OLE-DB provider library

RAISERROR('Test Only',  1,  2) WITH SETERROR
SELECT @@ERROR
RAISERROR (101,  1,  2) WITH SETERROR
SELECT @@ERROR
Here is the result set:
Msg 50000,  Level 1,  State 50000
Test Only
-----------
50000
(1 row(s) affected)
Msg 101,  Level 1,  State 101
Line 0: SQL syntax error.
-----------
101
(1 row(s) affected)

The first RAISERROR returns an @@ERROR value of 50000. The second returns the syntax error message used by Microsoft® SQL Server™ with an @@ERROR value of 101.

Formatting T-SQL Blocks:

T-SQL blocks have the following sections:

  1. Header
  2. Declaration section
  3. Executable section.
  4. Exception section.

PROCEDURE (Parameters)
RETURN DATATYPE
AS
Variables DATATYPE;

BEGIN
Executable_statements;
If err then goto Err_block
RETURN Value

Err_block:
Process error and return the error string
END
GO

Recommendations:

  1. All the executable statements after the BEGIN and error handling block are indented in from the BEGIN
  2. Include a blank line after each section.
  3. IS statement to be on a new line where everused.

Formatting SQL  statement:

SELECT
 INSERT
UPDATE
 DELETE
SELECT  FROM WHERE GROUP BY HAVING ORDER BY
INSERT INTO VALUES OR INSERT INTO SELECT
 UPDATE SET WHERE
DELETE FROM WHERE