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

SQL SERVER - Tips from the SQL Joes 2 Pros Development Series - SQL Server Error Messages - Day 27 of 35 joes2pros4 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?

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

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.

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

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.

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

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!”

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

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!”

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

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 which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Table Valued Functions – Day 26 of 35
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Structured Error Handling – Day 28 of 35

Related Posts

54 Comments. Leave new

  • Rajesh Mohanrangan
    August 27, 2011 1:04 pm

    Answers are 1 and 3

    Regards
    Rajesh
    From india

    Reply
  • yangamuniprasad
    August 27, 2011 1:42 pm

    Correct answer is options 1 & 3

    Thanks,
    Prasad yangamuni
    INDIA (PUNE)

    Reply
  • Sudeepta Ganguly
    August 27, 2011 1:57 pm

    The correct answers are:

    1. When a statement in SQL Server can not run
    3. When you issue a RAISERROR messge

    Sudeepta,
    India.

    Reply
  • 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.

    Reply
  • 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)

    Reply
  • Option 1 & 3 are Corect.

    Lahore , Pakistan.

    Reply
  • I think, correct answer is option “#1 & #3”.
    Option #2 is not applicable, because updating multiple records is (most of not all) about database.

    Reply
  • P.Anish Shenoy
    August 27, 2011 3:33 pm

    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

    Reply
  • Correct answer is 1 and 3.

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

    (Sale, Nigeria)

    Reply
  • option 1 and 3

    Reply
  • 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

    Reply
  • Srinivasan Prasanna
    August 27, 2011 6:29 pm

    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

    Reply
  • Options 1 and 3 are correct. If records are updated successfully in option 2, no error will be raised.
    Matt Nelson, USA

    Reply
  • Uday Bhoopalam
    August 27, 2011 7:25 pm

    Option 1 and 3 are correct answers when sql server will raise error.

    Uday Bhoopalam
    USA

    Reply
  • Nakul Vachhrajani
    August 27, 2011 7:30 pm

    The correct answers are #1 and #3:
    1. When a statement in SQL Server cannot run
    3. When you issue a RAISERROR message

    Country of residence: India

    Reply
  • 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

    Reply
  • dilipkumarjena
    August 27, 2011 8:36 pm

    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

    Reply
  • Rajneesh Verma
    August 27, 2011 10:19 pm

    Hi, Pinal
    Options 1 and 3 are correct Answers:

    Thanks…
    Rajneesh Verma
    (INDIA)

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

    Chetan – USA

    Reply
  • 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

    Reply

Leave a Reply

Menu