Handle error in your database base by using using a single procedure

How to handle all the error in sql server database, it is very easy, you will say the same after reading this article. If I ask the same question right now many of us start thinking how we can use it, do we need to create a table, procedure and what would be useful values which should be in this table and how to call the procedure from every procedure, how to page the parameter etc. stop thinking for now.

Here is the steps:

1. Create a single table
2. Create a procedure to log all the errors in table
3. Use Try .... Catch to log the error

So what are the information we need to log into the table, let's create the table:

CREATE TABLE [ErrorLog](
    [ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
    [ErrorNumber]   [int] NOT NULL,
    [ErrorMessage]  [varchar](8000) NULL,
    [Severity]  [int] NULL,
    [State]     [int] NULL,
    [Procedure] [varchar](200) NULL,
    [LineNo]    [int] NULL,
    [UserName]  [varchar](128) NULL,
    [Errortime] [datetime] NULL
) ON [PRIMARY]

If you can see closely we have everything here, now let's create a procedure to insert all these values without passing a single parameter, What? Without passing a any parameter? yes, see this:

Create Proc LogError
AS
  Insert Into ErrorLog(
    [ErrorNumber], 
    [ErrorMessage], 
    [Severity], 
    [State], 
    [LineNo],
    [Procedure], 
    [UserName], 
    [Errortime])

  SELECT error_number()
   , error_message()
   , error_severity()
   , error_state()
   , error_line()
   , error_procedure()
   , suser_sname()
   , GETDATE()

See the procedure, do you think we need to pass any value for this procedure, no because I took everything from the different different functions.

Time to use what we have created otherwise it's use less, let try to create procedure simply divide any number by zero(0) and check the table.

Create Proc testlog
AS

Begin Try
  select 10/0
End Try

Begin Catch
   EXEC Logerror
End Catch

In this procedure i try to divide 10 by 0, now execute the procedure test log

EXEC testlog

Now let's check our log table now

Select * From ErrorLog

and here is the log detail

ErrorLogID  ErrorNumber ErrorMessage    Severity    State   Procedure   LineNo  UserName    Errortime
1   8134    Divide by zero error encountered.   16  1   spTest  4   sa  2014-02-22 18:51:19.347

Other blogs you may like

Adding identity to an existing column in SQL Server

We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi   On 02 May 2013  Viewed: 1,084

some useful sql tricks

There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi   On 31 Mar 2013  Viewed: 924

Get precision and decimal value in SQL Server by using PARSENAME

In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty   On 26 Mar 2013  Viewed: 3,311

Available wildcards in sql server with examples

To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D   On 25 Feb 2013  Viewed: 666

Concatenate multiple columns in SQL Server with NULL value

When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi   On 13 Feb 2013  Viewed: 12,555