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.





(3)Right place for dropping temporary tables in SQL server T-SQL statement
Now we will see the right place for dropping temporary table in T-SQL statement in Sql server.
Right place for dropping temporary->
Drop after Begin TRY
  Drop before End TRY


  Drop in Catch block.









Author

Priti Kumari

I am technical blogger.I blogs at www.c-Sharpcorner.com and https://aspdotnetmyblog.blogspot.in/.

No comments:

Post a Comment