SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – SQL Server Error Messages – Day 27 of 35

Answer simple quiz at the end of the blog post and -

Every day one winner from India will get Joes 2 Pros Volume 4.

Every day one winner from United States will get Joes 2 Pros Volume 4.

SQL Server Error Messages

By now, most readers have likely learned that it is better to deal with problems early on while they are small.  SQL Server detects and helps you identify most errors before you are even allowed to run the code. For example, if you try to run a query against a table which does not exist, SQL Server informs you via IntelliSense while you’re coding the query or via an error message when you attempt to run the query. You also will get an error message if you try to insert a null value into a non-nullable field. This section will cover how SQL Server raises error messages.

Errors in SQL Statements

SQL Server will raise errors when the code you have written cannot or should not execute. For example, a table should not be created if one with the same name already exists. Also, you can’t run a stored procedure if the name you are calling does not exist.  Attempting to run such code will cause SQL to raise an error.

SQL Server raises an error whenever a statement cannot, or should not, complete its execution. For example, we know there is already an Employee table in the JProCo database. In the figure below we see code to create another Employee table.  If we attempt to run the code below, should SQL Server permit our longstanding Employee table to be overwritten?

Having a new, empty Employee table overwrite the one we are using would not be a good idea. In the figure below we see SQL Server prevented the accidental loss of the valuable data in the existing Employee table. When we run this, SQL Server alerts us that our code cannot be run and displays the reason in an error message.  Notice that this is error message 2714. Error severity levels range from a low of 0 to a maximum of 25, and the error severity level here is 16. We will discuss error severity more in tomorrows post.

A perfectly written table creation statement sometimes will work and under other conditions it may error out. With the code below the SometimesBad stored procedure will sometimes throw an error message. If SQL finds the TempStaff table, then it will attempt to run the table creation statement. Imagine the TempStaff table didn’t exist. In that case, nothing would happen. The IF EXISTS condition would be false so the CREATE TABLE statement would not be attempted.

CREATE PROC SometimesBad
AS
BEGIN
IF
EXISTS(SELECT * FROM sys.tables WHERE [name] = 'TempStaff')
CREATE TABLE TempStaff (TStaffID INT NOT NULL, TStaffName   VARCHAR(100) NULL)
END

We know one way to generate an error is to try creating a table which already exists. Another way is to try dropping a table which doesn’t exist. If you run the SometimesBad stored procedure, it will only throw an error if the TempStaff table already exists. For testing purposes, we want a stored procedure like SometimesBad to always throw an error message. Our next step will be to add an ELSE statement which says that we want to drop the table if it is not found.

Look closely at this code and recognize that it will always throw an error.  If the TempStaff table exists, then trying to execute a statement to create this table generates an error.  And if the TempStaff table doesn’t exist, then executing a statement which attempts to drop this table will similarly generate an error.

CREATE PROC AlwaysBad
AS
BEGIN
IF
EXISTS(SELECT * FROM sys.tables WHERE [name] = 'TempStaff')
CREATE TABLE TempStaff (TStaffID INT NOT NULL, TStaffName VARCHAR(100) NULL)
ELSE
DROP TABLE
TempStaff
END

If you were to create and run the AlwaysBad sproc, you would see that attempting to execute it will always result in an error message. During the sproc execution, SQL tried to create the TempStaff table which already existed. If the table is not present, then the same stored procedure would still throw an error.  Message 3701 indicates that you can’t drop the table because it does not exist.

EXEC AlwaysBad

Msg 3701, Level 11, State 5, Procedure AlwaysBad, Line 7
Cannot drop the table ‘TempStaff’, because it does not exist or you do not have permission.

RAISERROR

Again, we know that SQL Server will raise an error whenever a statement cannot, or should not, complete its execution. It’s also possible to define your own conditions where SQL Server does not encounter an error but nonetheless doesn’t run due to a situation which goes against company policy. For example, updating an employee’s payrate to below minimum wage is not a SQL error. However, in such a situation you would prefer that SQL Server generate an error message rather than allowing that code to execute.

Suppose you have a stored procedure named UpdateOneEmployee which changes one employee record at a time. The logic of this stored procedure will allow you to potentially update two employees with the same info. Since it is against company policy to update more than one employee record a time, it’s extremely unlikely that anyone would ever attempt to update multiple records at once. However, because SQL Server has no restriction against updating or many records in one transaction, you want to add a layer of protection to help enforce company policy. This is a case where you don’t want SQL Server to allow this update, even though SQL Server doesn’t define it as an error. To accomplish the needful, you can raise your own error message based on conditions which you define.

ALTER PROC SetEmployeeStatus @EmpID INT, @Status VARCHAR(20)
AS
BEGIN
UPDATE
Employee SET [Status] = @Status
WHERE EmpID = @EmpID
END

We can confirm the status change by querying the Employee table. Passing the values 1 and ‘On Leave’ into SetEmployeeStatus changes the Status field for Employee 1 to ‘On Leave.’ This changes the value of EmpID 1. Let’s execute this statement for an employee which doesn’t exist.  Notice that our result is not actually an error. The statement runs alright, but no rows are affected because no records in JProCo’s Employee table meet the criteria.

This is not a SQL Server error since it’s OK to update zero records. But what if the 51 was a typo?  What if the intent was to update the status for Employee 11 or Employee 21?  In that case, it would be helpful to program your code to alert you if you accidentally attempted to run a statement for a non-existent EmpID. Let’s alter the stored procedure by adding two more statements.  If we see that no rows are affected then we will raise a level 16 error that alerts us, “Nothing was done!”

With the sproc updated, let’s run our prior EXEC statements for EmpID 1 and EmpID 51.  EmpID 1 runs the same as it did previously. Here you can see that calling on the stored procedure and passing in 51 for the EmpID returns our user-defined error message. The severity is level 16 and the message says “Nothing was done!”

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLProgrammingChapter8.1Setup.sql script from Volume 4.

Question 27

When does SQL Server always raise an error message? (Choose two)

  1. When a statement in SQL Server cannot run
  2. When multiple records are updated in one table
  3. When you issue a RAISERROR message

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Every day one winner from India will get Joes 2 Pros Volume 4.
Every day one winner from United States will get Joes 2 Pros Volume 4.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

60 thoughts on “SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – SQL Server Error Messages – Day 27 of 35

  1. Correct answers are 1 and 3: SQL Server always raise an error message when a statement in SQL Server cannot run or when someone issues a RAISERROR message.
    Rene Castro
    El Salvador

    Like

  2. When a statement cannot run – SQL Server will tell you why it cannot run that particular statement, either there is some kind of violation such as security, constraint/rule or the syntax is wrong or similar
    When you issue a raiseerror message – the raiseerror message is specifically intended for user defined error situations such as the one above, updating multiple records. When a raiserror is encountered sql does not worry about underlying cause just raises the error.
    ———————-
    From USA

    Like

  3. Answers are 1 and 3

    SQL will raise an error “When a statement in SQL Server cannot run” but you can also write your own messages to alert when certain things happen that you don’t want. This is where #3 (When you issue a RAISERROR message) comes in.

    USA

    Like

  4. Hi,

    options 1 and 3 fit in. Options 2 is incorrect because updating/deleting 0 to n rows will depend entirely upon number of rows returned by the WHERE clause. Therefore, multiple update might be a logical error.

    SQL Server throws an error when:
    1. it encounters incorrect syntax or such statements which it cannot execute (Option 1)
    2. We code an error raising statement (Option 3)

    Ramakrishnan RS
    Mysore, India

    Like

  5. Ans :1 and 3

    1.When a statement in SQL Server cannot run
    3.When you issue a RAISERROR message

    When a sql statement can not run it will through an error message due to some
    violation like security, constraint/rule or the syntax is wrong or for an object which dose not exits or similar.So option 1 is correct.
    if we manually wants to raise an error which is not sql server error like we don’t
    want to drop a table for that we write a ddl trigger there we roll back the drop action an raise an error.sql server always throw that error. so option 3 is also correct.
    But update multiple record in a table is not an sql server error it allows user to update multiple record. In that case sql server does not through an error except we does not raise it manually. so option 2 is incorrect.

    Partha,
    India

    Like

  6. SQL Server always raise an error message on following 2 occasions

    1. When a statement in SQL Server cannot run
    3. When you issue a RAISERROR message

    When a statement like dropping a table which does not exists or creating a table which is already present, then SQL server would raise an error message.

    Also when we explicitly specify RAISERROR message then the same is taken care by SQL server if any error occurs related to the same.

    Kedar – India

    Like

  7. 1. When a statement in SQL Server cannot run
    3. When you issue a RAISERROR message

    These are the correct options. SQL Server raises error where some erroneous condition occurs or user raises error on particular special event.
    Error occurs when :

    a) Database object/Column not Exists
    b) Constraints Violation (Primary key, foreign key, not null, check etc.)
    c) Syntax errors
    d) Column not added in “group by ” clause but used in select list with aggregate functions.
    e)User don’t have rights to perform specific operation. etc.

    Thanks,

    Shirish,
    India

    Like

  8. Answer is 1 and 3. SQL throws error in these two scenarios and regarding option 2 it’s about multiple record update which is allowed for a table in SQL.

    Sathya, chennai, India

    Like

  9. Correct Answers are # 1 & 3.

    1. When a statement in SQL Server cannot run.
    3. When you issue a RAISERROR message.

    SQL Server always raise an error message when a statement in SQL Server cannot run or when someone issues a RAISERROR message.

    Gopalakrishnan Arthanarisamy
    Unisys, Bangalore, India.

    Like

  10. ANS : 1 and 3
    (1) When a statement in SQL Server cannot run
    (3) When you issue a RAISERROR message

    Explanation
    Ans 1:
    SQL Server will raise errors when the code you have written cannot or should not execute. For example, a table should not be created if one with the same name already exists. Also, you can’t run a stored procedure if the name you are calling does not exist.

    Ans 3:
    when we explicitly specify RAISERROR message.
    Raise your own error message based on conditions which you define.

    Like

  11. ANS : 1 and 3
    (1) When a statement in SQL Server cannot run
    (3) When you issue a RAISERROR message

    Explanation
    Ans 1:
    SQL Server will raise errors when the code you have written cannot or should not execute. For example, a table should not be created if one with the same name already exists. Also, you can’t run a stored procedure if the name you are calling does not exist.

    Ans 3:
    when we explicitly specify RAISERROR message.
    Raise your own error message based on conditions which you define.

    Thanks,
    Mitesh Modi
    (India)

    Like

  12. Hi Sir,

    The correct answers are 1 and 3.

    1. When a statement in SQL Server cannot run

    When we run a sql statement with creating a table which is already present
    or running a drop statement on a table which is not present, SQL server will throw an error.

    There can many such instances have just specified two examples.

    3. When you issue a RAISERROR message

    RAISERROR is used to give custom error messages when sql server finds some error in executing the
    statements.

    P.Anish Shenoy,
    INDIA, Bangalore, Karnataka

    Like

  13. Hi,

    Option 1 and 3 are correct.

    Option 2 is invalid because there no error is raised while inserting multiple records in 1 table

    Thanks

    Sudhir Chawla
    New Delhi, India

    Like

  14. Correct answers are 1 and 3.
    Reasons:
    For choice #1 – When SQL Server cannot run a SQL statement (DDL or ill formed DML), it will throw an error.

    For Choice #3 – When you issue an raiseerror – SQL Server will do just that.

    Srinivasan Prasanna
    Minneapolis, USA

    Like

  15. Basically, SQL Server will raise errors when the code wrote cannot or should not execute, therefore the correct answer should be #1 When a statement in SQL Server cannot run………..Furthermore, SQL will return error when you establish and issue the RASISERROR message – Also, #3 is correct When you issue a RAISERROR message

    USA

    Like

  16. The correct options are #1 and #3

    {1} When a statement in SQL Server cannot run
    {3} When you issue a RAISERROR message

    Apart from that there can be many a reasons like when syntax error occurs or inserting more columns than containing etc. many reasons.

    This is DILIP KUMAR JENA
    From : India

    Like

  17. Hi Pinal,

    Challenge:
    Question 27
    When does SQL Server always raise an error message? (Choose two)
    1. When a statement in SQL Server cannot run
    2. When multiple records are updated in one table
    3. When you issue a RAISERROR message

    Correct Answer:
    The correct choices are #1 and #3.

    Explanation:
    If SQL Server cannot run a statement, then it will throw an error. This could be due to a syntax error such as the following: “selec * from filing”

    Because the “t” is missing from the Select clause, the following error was thrown by SQL Server:

    Msg 102, Level 15, State 1, Line 3
    Incorrect syntax near ‘*’.

    As Pinal showed, if you try to delete an non-existent object or try to create an object that already exists, again, SQL Server would throw an error.

    The above reflect choice #1 above.

    Also, SQL Server will raise an error when a RAISEERROR is encountered (choice #3 above).

    Choice #2 will not throw an error unless the programmer added a RAISERROR when multiple records were updated and the programmer was expecting only one row to be updated.

    Country:
    United States
    (wet and windy Connecticut, facing the power of Hurricane Irene!)

    Thanks for the knowledge!

    Regards,

    Bill Pepping

    Like

  18. This is a quite simple question and correct answers are 1 and 3

    When a statement in SQL Server cannot run – this will result in error

    When you issue a RAISERROR message – this is the error we generate on purpose

    I am from USA and sorry for answering a bit late

    Like

  19. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 ourney to SQLAuthority

  20. Correct option : Answer 1 7 3

    1.When a statement in SQL Server cannot run
    3.When you issue a RAISERROR message

    Chennai, TamilNadu, India

    Like

  21. Answer is: 1. When a statement in SQL Server cannot run.
    Or should not run due to duplicate table names, or deletion of
    nonexistant table.
    3. When you issue a RAISERROR message.
    To ensure compliance with or non-violation of a company policy.

    Ron A. Farris
    USA

    Like

  22. The correct answers are 1 and 3

    When a statement in SQL Server cannot run
    When you issue a RAISERROR message

    Option 2 is not true. Updating multiple records in one table does not produce an error.

    Country of Residence: USA

    Like

  23. Pingback: SQL SERVER – Programming and Development – Book Available for SQL Server Certification Journey to SQLAuthority

  24. Pingback: SQL SERVER – T-SQL Errors and Reactions – SQL in Sixty Seconds #002 – Video « SQL Server Journey with SQL Authority

  25. Pingback: SQL SERVER – T-SQL Errors and Reactions – Demo – SQL in Sixty Seconds #005 – Video « SQL Server Journey with SQL Authority

  26. Pingback: SQL SERVER – Expanding Views – Contest Win Joes 2 Pros Combo (USD 198) – Day 4 of 5 « SQL Server Journey with SQL Authority

  27. Pingback: SQL Server 2012: THROW your errors | Vinod Kumar (Blog home)

  28. Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

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