Error Handling in SQL Server 2005

TRY…CATCH construct is used for error handling in side T-SQL code. A TRY…CATCH construct consists of 2 blocks. One is TRY block that is immediately followed by a CATCH block. TRY…CATCH construct can be nested. When an error condition is identified in TRY block, the control is immediately transferred to CATCH block where the error can be handled.

The following code represents a basic TRY…CATCH construct.

BEGIN TRY

SELECT 1/0;

END TRY

BEGIN CATCH

      SELECT ERROR_MESSAGE();

END CATCH

Error Functions:

The following error functions can be used inside a TRY…CATCH construct to get the information about the error that is occurred.

ERROR_NUMBER() returns the error number.

ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.

ERROR_SEVERITY() returns the error severity.

ERROR_STATE() returns the error state number.

ERROR_LINE() returns the line number inside the routine that caused the error.

ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

TRY…CATCH with RAISERROR

RAISERROR can be used inside a TRY…CATCH construct for error handling. RAISERROR generates error message using user defined messages that is stored inside sys.messages catalog view or build a message dynamically.

The following code returns an error message and the WITH LOG statement causes the error to be logged into windows application log. Using event viewer, you can view the log.

BEGIN TRY

    RAISERROR ('Error raised in TRY block.', 16,10) WITH LOG;

END TRY

BEGIN CATCH

    SELECT Error_Message() as Message_text,

               Error_Severity() as Severity,

               Error_State() as State

END CATCH

Questions:

1. What types of error cannot be handled by TRY…CATCH construct. Choose all correct answers from the following:

a. All DDL statement errors

b. All DML statement errors

c. Divide by Zero error

d. Syntax errors that prevent a batch from executing.

Answers:

1. d.

Questions on SQL JOINS

A join will allow us to view data from related tables in a single result set.

INNER JOIN:

• In an inner join, Only rows with values satisfying the join condition in the common column are displayed.
• It is default join. So 'INNER' keyword can be ignored.

OUTER JOIN:

• In an outer join, rows are returned even when there are no matches through the JOIN criteria on the second table.
• Outer join is of three types: Left, Right, Full outer joins

LEFT OUTER JOIN:

• A left outer join or a left join returns results from the table mentioned on the left of the JOIN keyword irrespective of whether it finds matches or not.
• If the ON clause matches 0 records from table on the right, it will still return a row in the result—but with NULL in each column.

RIGHT OUTER JOIN:

• A right outer join or a right join returns results from the table mentioned on the right of the JOIN keyword irrespective of whether it finds matches or not.
• If the ON clause matches 0 records from table on the left, it will still return a row in the result—but with NULL in each column.

FULL OUTER JOIN:

• A full outer join will combine results of both left and right outer join. Hence the records from both tables will be displayed with a NULL for missing matches from either of the tables.

CROSS JOIN:

• A cross join also known as cartesian product between two tables joins each row from one table with each row of the other table.
• A cross join does not include 'on' clause.

Two tables are created and values are inserted using following SQL statements.

CREATE TABLE A (ID numeric)

INSERT INTO A VALUES(1)

INSERT INTO A VALUES(2)

INSERT INTO A VALUES(3)

CREATE TABLE B (ID numeric)

INSERT INTO B VALUES(4)

INSERT INTO B VALUES(5)

INSERT INTO B VALUES(6)

Using the above tables, try to answer the following questions.

Questions:

1. What will be output of following query?

SELECT A.* from A inner join B on A.ID=B.ID

(A) 1, 2, 3, NULL, NULL, NULL
(B) 1, 2, 3
(C) NULL, NULL, NULL
(D) 0 Rows


2. What will be output of following query?

SELECT A.* from A left outer join B on A.ID=B.ID
(A) 1, 2, 3, NULL, NULL, NULL
(B) 1, 2, 3
(C) NULL, NULL, NULL
(D) 4, 5, 6


3. What will be output of following query?

SELECT A.* from A right outer join B on A.ID=B.ID

(A) 1, 2, 3, NULL, NULL, NULL
(B) 1, 2, 3
(C) NULL, NULL, NULL
(D) 4, 5, 6


4. What will be output of following query?

SELECT A.* from A full outer join B on A.ID=B.ID

(A) 1, 2, 3, NULL, NULL, NULL
(B) 1, 2, 3
(C) NULL, NULL, NULL
(D) 4, 5, 6


5. What will be output of following query?

SELECT A.* from A cross join B

(A) 1, 2, 3, NULL, NULL, NULL
(B) 1, 2, 3
(C) NULL, NULL, NULL
(D) 1, 2, 3, 1, 2, 3, 1, 2, 3

Show Answers:

1. (D)
2. (B)
3. (C)
4. (A)
5. (D)


Hide Answers (X).

Assorted Questions from SQL Server Forums

1. Writing query without using DISTINCT keyword:

Below is my table data.I want the result as 3.

col1
------
4
4
66
66
88

Result as
count
----
3

How to write a Query without using Distinct. Because I am running this query in SQL Mobile. In Sql Mobile edition there is no "distinct" keyword support.

The solution is as follows:

CREATE TABLE Table1(Col1 int);

INSERT INTO Table1
SELECT (4)
  UNION ALL SELECT (4)
  UNION ALL SELECT (66)
  UNION ALL SELECT (66)
  UNION ALL SELECT (88);

SELECT COUNT(*) FROM 
              (SELECT 0 AS c1
               FROM Table1 GROUP BY Col1) AS t1;
2. Which is better SP or Views?

Asking which is faster or better is like comparing the speed of a car with that of a boat - the speed difference is irrelevant, since you'll always prefer the boat if you travel over water, and always the car for travels over land.


VIEW: A view is a "virtual" table consisting of a SELECT statement, by means of "virtual"
I mean no physical data has been stored by the view -- only the definition of the view is stored inside the database; unless you materialize the view by putting an index on it.

1. By definition you can not pass parameters to the view.
2. NO DML operations (e.g. INSERT, UPDATE, and DELETE) are allowed inside the view; ONLY SELECT statements.

Most of the time, view encapsulates complex joins so it can be reusable in the queries or stored procedures. It can also provide level of isolation and security by hiding sensitive columns from the underlying tables.

Stored Procedure: A stored procedure is a group of Transact-SQL statements compiled into a single execution plan or in other words saved collection of Transact-SQL statements.

Here is a good summary from SQL MVP Hugo Kornelis (was posted in a newsgroup few years ago)
　
A stored procedure:

• accepts parameters
• can NOT be used as building block in a larger query
• can contain several statements, loops, IF ELSE, etc.
• can perform modifications to one or several tables
• can NOT be used as the target of an INSERT, UPDATE or DELETE statement.

　
A view:

• does NOT accept parameters
• can be used as building block in a larger query
• can contain only one single SELECT query
• can NOT perform modifications to any table
• but can (sometimes) be used as the target of an INSERT, UPDATE or DELETE statement.

　

>>Also is view plans are in plan cache or not? where are SPs plans are stored in cache and are always >>best for performance boost?

>>where are SPs plans are stored in cache and are always best for performance boost?

Yes execution plan for sps are stored in "plan cache" and in general it can boosts performance..

3. Stored Procedure - Standards and Best practices checking

Looking for an utility which can review stored .procedures for Standards, best practices etc. Env is SQL Server 2005 I can offer some manual hints:

4. Return only a single result set - unlike sp_monitor

http://www.sqlusa.com/sqlformat/http://www.sqlusa.com/bestpractices/training/scripts/parametersniffing/

1. Naming: prefix with "usp" or "sproc" - for example, uspInventoryUpdate

2. Add sproc comment block after "AS"

3. Make it safe from parameter sniffing:

5. Avoid nesting stored procedures because error control is difficult

6. Comment where needed

7. Format code for readability - auto formatter:

Simple Questions on Stored Procedures

The following mind map diagrammatically briefs about stored procedures. For an in-depth learning about the concepts, do search books on-line topics for stored procedures.

Questions:

1. Which of the following commands, a stored procedure can execute?

(a) USE
(b) Set Showplan_Text on
(c) Set Showplan_All on
(d) Both (b) and (C)
(e) None of the above.


2. You have noticed that a stored procedure is recompiled on each execution. The cause of recompilation is a simple query statement. How will you optimize the performance of your stored procedure with minimum effort?

(a) Create an additional stored procedure, and include the query that causes the recompilation. Call the new stored procedure from the new one.
(b) Add the RECOMPILE query hint to the query statement that causes the recompilation.
(c) Modify your stored procedure, and include the WITH RECOMPILE option in its definition.
(d) Use the sp_recompile system stored procedure to force the recompilation of your stored procedure the next time it runs.


3. Which one of the following option regenerates the query plan each time a stored procedure is executed?

(a) Recompile
(b) Encryption
(c) Execute As
(d) Varying


4. Which of the following can be used to recompile a stored procedure everytime it is running? (Choose all that apply.)

(a) Modify your stored procedure, and include the WITH RECOMPILE option in its definition.
(b) Add the RECOMPILE query hint to one of the stored procedure statements.
(c) Use the sp_recompile system stored procedure.
(d) Specify the WITH RECOMPILE option when you execute the stored procedure.


5. Your RTSdev database alerts you that the transaction log file is almost full. You suspect that one of the stored procedures has left a transaction open. Which one of the following can be used to ensure it?

(a) Execute DBCC TLOGFULL against the RTSdev database.
(b) Execute DBCC OPENTRAN against the temp database.
(c) Execute DBCC TLOGFULL against the temp database.
(d) Execute DBCC OPENTRAN against the RTSdev database.


6. The following script is used to create a stored procedure:

DECLARE @UserID int
BEGIN TRY
 BEGIN TRANSACTION
  DELETE From dbo.Users
  WHERE UserID = @UserID
 COMMIT TRANSACTION
END TRY
BEGIN CATCH
 DECLARE @errMsg nvarchar(300)
 SELECT @errMsg = ERROR_MESSAGE()
END CATCH

When execute the SP, you observe that it leaves open transactions. How will you modify the stored procedure?

(a) Add a ROLLBACK TRANSACTION to the CATCH block.
(b) Add a ROLLBACK TRANSACTION to the TRY block.
(c) Add a COMMIT TRANSACTION to the CATCH block.
(d) Add a COMMIT TRANSACTION to the TRY block.


7. If you need to create a stored procedure to perform some calculation-intensive operations and then return the results as quickly and efficiently as possible. How will you achieve that?

(a) Use parameters within the stored procedures.
(b) Use a TRY CATCH block within the stored procedure.
(c) Use a CLR-integrated stored procedure.
(d) Create the stored procedure so it recompiles each time it is run.


Show Answers:

1. (a)
2. (b)
3. (a)
4. (a), (d)
5. (d)
6. (a)
7. (c)


Hide Answers (X).

Simple Questions on Transactions

A transaction is a group of statements that are combined into a single logical unit. All statements succeed as a whole, or if any one of the statement fails, they all fail as a whole.

BEGIN TRAN --> Marks the starting point of a transaction.
SAVE TRAN Save_point --> Sets a new save point with in a transaction.
COMMIT TRAN --> Marks the end of a transaction and makes the changes permanent.
ROLLBACK [TRAN] [Save_point] --> Rolls back a transaction to the starting point or to the specific save point.



1. The following script if executed, what will be the output if you have only 200 Rupees?

IF Cash > 200
   'Buy Fruits'
   'Buy Snacks'

(a) Buy Fruits, Buy Snacks.
(b) Buy Snacks.
(c) None of the statements will execute.


2. The following script if executed, what will be the output you have only 200 Rupees?

IF Cash > 200
    BEGIN
        'Buy Fruits'
        'Buy Snacks'
    END

(a) Buy Fruits, Buy Snacks.
(b) Buy Snacks.
(c) None of the statements will execute.


3. Which one of the following function returns the open transaction count?

(a) @@ERROR
(b) @@FETCH_STATUS
(c) @@TRANCOUNT
(d) @@ROWCOUNT


4. Consider using nested transactions, if any one transaction rollbacks what will happen to other transactions with in the nesting?

(a) All other transactions will be committed except the one which rollback.
(b) All the transactions will be rollback regardless of the nesting level.
(c) None of the transactions will execute.
(d) Creates open transactions.


5. An open transaction prevents others from viewing the data that is modified, which command can be used to make the modifications permanent?

(a) UPDATE TRAN
(b) DELETE TRAN
(c) SELECT TRAN
(d) COMMIT TRAN


6. When using a TRY…CATCH block, which function helps to find out the description of the error?

(a) ERROR_SEVERITY
(b) ERROR_STATE
(c) ERROR_MESSAGE
(d) ERROR_NUMBER


Show Answers:

1. (b)
2. (c)
3. (c)
4. (b)
5. (d)
6. (c)

Hide Answers (X).

Questions based on Collation settings

If you right click a database and view the properties, you can look for the collation setting as shown in the figure. The highlighted ‘CI’ represents the database is case-insensitive. Suppose if it is ‘CS’ then your database is case sensitive.






For example, the following query returns different results in different collation settings.

SELECT * FROM TestTab WHERE CharCol LIKE N'abc'


Following query creates a column with ‘Case In-sensitive’

CREATE TABLE TestTab
(PrimaryKey int PRIMARY KEY,
CharCol char(10) COLLATE French_CI_AS
)


1. At what level you can specify the COLLATE clause. Choose all correct answers.

a. Creating or altering a database.
b. Creating or altering a table column.
c. Casting the collation of an expression.
d. All of the above.



2. What function is used to retrieve a list of all the valid collation names for Windows collations and SQL collations

a. fn_sqlvarbasetostr()
b. fn_my_permissions()
c. fn_helpcollations()
d. fn_dblog()


3. Which of the following datatypes are used with COLLATE clause.

a. char, varchar, text
b. nchar, nvarchar, and ntext
c. int, smallint, tinyint
d. datetime, money


Show Answers:

1. (d)
2. (c)
3. (a), (b)



Or Hide Answers (X).

Reference: Books on line: COLLATE (Transact-SQL)

Basic questions on SELECT statement

1. Which of the following data types cannot be used with ORDER BY clause.

(a) Text
(b) ntext
(c) Image
(d) xml


2. Which of the following can’t be used in an indexed view?

(a) Getdate()
(b) DateDiff()
(c) HOUR()
(d) MINUTE()

3. To get cartesian product from two tables, what type of join you will use.

(a) Outer Join
(b) Full Join
(c) Cross Join
(d) Self-Join

4. What data type allows you to use upto 2 GB and allow you to use operators and functions.

(a) Binary
(b) Varbinary
(c) Varchar
(d) Varchar(max)

5. Select EmployeeID,
convert (Varchar, DateOfJoining, 101) AS DateOfJoining
FROM Employee
ORDER BY DateOfJoining

When executing the above query, if dates considered were 07/01/2010 and 08/01/2009, which date will appear first.

(a) 08/01/2009
(b) 07/01/2010

6. Select EmployeeID,
convert (Varchar, DateOfJoining, 101) AS [Date Of Joining]
FROM Employee
ORDER BY DateOfJoining

When executing the above query, if dates considered were 07/01/2010 and 08/01/2009, which date will appear first.

(a) 08/01/2009
(b) 07/01/2010

Show Answers:

3. (C) The result set returned by a cross join is known as a cartesian product. A cross

join joins each row from the first table with each row from the second table.

4. (D)
5. (B)
6. (A)


Or Close (X).