Development

SQL Server – Conditions when value of @@error changes

Hi friends, when we do error handling in our SQL code we use different methods like TRY/CATCH, RAISERROR etc. to return the error messages. We also use global variable @@error to print the error value of the last T-SQL statement executed. But many folks doesn’t know that value of @@error gets reset with every execution of T-SQL statement. Today I will show you how @@error value gets changes automatically with every execution.

Let’s executed the below code to see what error value will return in output.

SELECT ‘Print error’

SELECT 1/0

Print @@error

1

In output we can see error value 8134 below the error message. This values comes from the system table sys.sysmessages

SELECT error as Error_Number, Severity, Description, msglangid as Language_Code

FROM sys.sysmessages

WHERE error = 8134  and msglangid = 1033

2.jpg

If we want to return our own custom error message then SQL has provide us facility to do that and we can use RAISERROR command for that.

Now let me add another SQL statement between that and re execute the query to see whether I am getting same value or not.

SELECT ‘Print error’

SELECT 1/0

Print @@error

SELECT ‘error’

PRINT @@error

As most of us still expecting the same value 8134 for the second print statement also.

What will be the error value for the second print statement?

3

Second print statement will return 0.

Why?

As we know that @@error return the error value of the last statement executed and it returns 0 if statement gets executed successfully.

Here SELECT ‘error’ is the last statement for the second Print @error statement and it’s always gets executed successfully so it returns 0.

That’s all for the day folks 🙂

Advertisements

3 thoughts on “SQL Server – Conditions when value of @@error changes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s