Exception Handling and Standard of writing SQL query in SQL server

In this article I will discuss about exception handling in SQL and also show you standard of writing SQL query.

We will see below point in this article.

(1)Standard of writing SQL query

(2)Exception handling in SQL stored procedure

(3)Right place for dropping temporary tables in SQL server

(1)   Standard of writing SQL query- Here you can see standard format for SQL Stored procedure.

-- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:        name of author -- Creation date: 25-05-2015 -- =============================================  IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE TYPE = 'P' AND NAME = 'NameOfStoredProcedure')             DROP PROCEDURE NameOfStoredProcedure GO CREATE PROCEDURE NameOfStoredProcedure     @PARAM1 datatype, @PARAM2 datatype                         AS BEGIN                         SET NOCOUNT ON; BEGIN TRY ----------------------------------------------------------------------------------------------------------  --Drop all temproray table here which you have created in overall stored procedure--------- IF OBJECT_ID( 'TEMPDB..#temporarytableName' ) IS NOT NULL BEGIN                         DROP TABLE   [#temporarytableName]; END IF OBJECT_ID( 'TEMPDB..#temporarytableName1' ) IS NOT NULL BEGIN                         DROP TABLE   [#temporarytableName]; END    ----------------------------------------------------------------------------------------------------------   --Create Temproray table here------ SELECT col1,col2,col3            INTO #temporarytableName FROM Tablename SELECT col1,col2,col3            INTO #temporarytableName1 FROM Tablename1 --------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- --Write T-SQL  Query Here------- ----------------------------------------------------------------------------------------------------------   ------------------------------------------------------------------------------------------------------------ --Again Drop all temproray table here which you have created in overall stored procedure IF OBJECT_ID( 'TEMPDB..#temporarytableName' ) IS NOT NULL BEGIN                         DROP TABLE   [#temporarytableName]; END IF OBJECT_ID( 'TEMPDB..#temporarytableName1' ) IS NOT NULL BEGIN                         DROP TABLE   [#temporarytableName]; END ---------------------------------------------------------------------------------------------------------------- END TRY BEGIN CATCH                         INSERT INTO NameofTable_ErrorLog(ERROR_PROCNAME, ERROR_LINENUM,ERROR_TYPENUM,ERROR_LOGMSG,ERROR_LOGDATE)                         VALUES(ERROR_PROCEDURE(),ERROR_LINE(), ERROR_NUMBER(),ERROR_MESSAGE(),GETDATE())                         ---------------------------------------------------------------------------------------------------------- --Again Drop all temproray table in catch block which you have created in overall stored procedure IF OBJECT_ID( 'TEMPDB..#temporarytableName' ) IS NOT NULL BEGIN                         DROP TABLE   [#temporarytableName]; END IF OBJECT_ID( 'TEMPDB..#temporarytableName1' ) IS NOT NULL BEGIN                         DROP TABLE   [#temporarytableName]; END ---------------------------------------------------------------------------------------------------------------------                         RAISERROR('PROBLEM   OCCURED WITH STORE PROCEDURE NAMED NameOfStoredProcedure. THIS WILL BE LOGGED.', 16, 1)                                                      END CATCH         END

(2)Exception handling in SQL stored procedure- Now I will show you how you can handle exception in T-SQL statement.

-Initially you need to create ERROROCONTAINER table in your database.

CREATE TABLE ERROROCONTAINER(        [ROWID] [INT] IDENTITY(1,1) PRIMARY KEY,        [ERRORPROCNAME] [VARCHAR](50) NOT NULL,        [ERRORLINENUM] [INT] NOT NULL,        [ERRORTYPENUM] [INT] NOT NULL,        [ERRORLOGMSG] [NVARCHAR](MAX) NOT NULL,        [ERRORLOGDATE] [DATETIME] NOT NULL )

  -Then we see how to use this table for exception handle. Here I will use this table in catch block of stored procedure.  

 In catch block I have used ERROROCONTAINER table and insert error in this table. Whenever error occurs error logged in this table so that we can easily track error and fix.