Handling Errors and Exceptions using Try-Catch: SQL

When you execute a query, it is parsed for syntactical errors before execution. If the syntax is correct, it is compiled and executed. Sometimes, due to factors, such as incorrect data, an error can occur during execution even if the query is syntactically correct. The errors that occur at run time are known as exceptions.

Consider an example. There is a primary key constraint applied on the EmployeeID attribute of the Employee table. When you try to insert an employee ID that already exists in the table, an error occurs while executing the INSERT statement.

When a database server provides database support to a business application, errors generated while executing the SQL statements can be handled in two ways:

• By adding error-handling code to the batch by using the TRY-CATCH construct.
• By returning the error to the business application by using the RAISERROR statement and handling the error in the application.

Using TRY-CATCH

A TRY-CATCH construct includes a TRY block followed by a CATCH block. A TRY block is a group of SQL statements enclosed in a batch, stored procedure, trigger, or function. If an error occurs in any statement of the TRY block, the control is passed to another group of statements that is enclosed in a CATCH block.

A CATCH block contains SQL statements that perform some operations when an error occurs. Therefore, an associated CATCH block must immediately follow a TRY block, as shown in the following syntax:

TRY
<SQL statements>
…
CATCH
<SQL statements>
…
END CATCH

If there are no errors in the code that is enclosed in a TRY block, the control is passed to the statement immediately after the associated END CATCH statement. In this case, statements enclosed in the CATCH block are not executed.

The TRY ……..CATCH constructs can be nested. Either a TRY block or a CTCH block can contain nested TRY…. CATCH constructs. A CATCH block can contain an embedded TRY… CATCH construct to handle errors encountered by the CATCH code.

In the CATCH block, you can use the following system functions to determine information about the errors:
ERROR_LINE0: returns the line number at which the error occurred.

• ERROR_MESSAGE0: specifies the text of the message that would be returned to the application. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
• ERROR_NUMBER0: returns the error number.
• ERROR_PROCEDURE0: returns the name of the stored procedure or trigger in which the error occurred. This function returns NULL if the error did not occur within a stored procedure or trigger.
• ERROR_SEVERITY0: returns the severity.
• ERROR_STATE0: returns the state of the error.

Consider an example. The EmployeeID attribute of the Employee table in the Adventure Works database is an IDENTITY column and its value cannot be specified while inserting a new record. In this case, if you specify the value for the EmployeeID in the INSERT statement, an error will be generated.

To handle such run-time errors, you can include the insert statement in a TRY block and send the control to the following CATCH block where the error information is displayed, as shown in the following statements:

BEGIN TRY
INSERT INTO [AdventureWorks] . [Person] . [Contact]
VALUES (0, null, ‘Robert’, ‘J’ , ‘Langdon’, NULL, ‘rbl@adventure-works.com’, 0, ‘1 (11) 500 555-0172’, ‘9E685955-ACD0-4218-AD7F-60DDF224C452’, ‘2a31OEw=’, NULL, newid( ), getdate ( ))

INSERT INTO [AdventureWorks] . [HumanREsources] . [Employee]
VALUES (‘AS01AS25R2E365W’, 19979, ‘robert1’, 16, ‘Tool Designer’, ‘1972-05-15’, ‘S’, ‘M’, ‘1996+-07-31’, 0, 16, 20, 1, newid( ), getdate ( ))

How to use Case and While statement in Batches: SQL

Database developer can use the CASE as well as While statement in situation where several conditions need to be evaluated.

Using CASE statement

The CASE statement evaluates a list of conditions and returns one of the possible results. You can use the IF statement to do the same task. However, you can use a CASE statement when there are more than two conditions that check a common variable for different values. The syntax of the CASE statement is:

CASE
WHEN Boolean_expression THEN expression
[ [WHEN Boolean_expression THEN expression] […..] ]
[ELSE expression]
END
Where,

• Boolean_expression specifies a bollean expression that is evaluated when using the CASE construct.
• Expression is the resultant expression that is executed when the Boolean expression evaluates to TRUE. This can be a constant, a column name, a function, a query, or any combination of arithmetic, bit-wise, and string operators.

In a simple CASE construct, a variable or an expression is compared with the Boolean expression in each WHEN clause. If any of these expressions evaluate to TRUE, then the expression specified with the THEN clause is executed. If the expression does not evaluate to TRUE, the expression with the ELSE statement is executed.

Consider the following example where a case construct is included in the SELECT statement to display the marital status as ‘Married’ or Single’:

SELECT EmployeeID, ‘Marital status’ =
CASE MaritalStatus
WHEN ‘M’ THEN ‘Married’
WHEN ‘S’ THEN ‘Single’
ELSE ‘Not specified’
END
FROM HumanResources.Employee
GO

Using the While Statement

You can use the WHILE statement in a batch to allow a set of T-SQL statement to execute repeatedly as long as the given condition holds true. The syntax of the WHILE statement is:

WHILE Boolean_expression
{sql_statement | statement_block}
[BREAK]
{sql_statement | statement_block}
[CONTINUE]
Where,

• Boolean_expression is an expression that evaluates to TRUE or FALSE.
• Sql_statement is any SQL statement.
• Statement_block is a group of SQL statements.
• BREAK causes the control to exit from the WHILE loop.
• CONTINUE causes the WHILE loop to restart, skipping all the statements after the CONTINUE keyword.

The SQL Server provides the BREAK and CONTINUE statements to control the statement within the WHILE loop. The BREAK statement causes an exit from the WHILE loop. Any statements that appear after the END keyword, which marks the end of the loop, are executed after the BREAK statement is executed. The CONTINUE statement causes the WHILE loop to restart, skipping any statements after this statement inside the loop.

Consider the following example where the HR department of AdventureWorks, Inc. has decided to review the salary of all the employees. As per the current HR policy, the average hourly salary rate of all the employees should be approximately $20. You need to increase the hourly salary of all the employees until the average hourly salary reaches near $20. In addition, you need to ensure that the maximum hourly salary should not exceed $127.

WHILE (SELECT AVG(Rate) +1 FROM HumanResources.EmployeePayHistory) <20
BEGIN
UPDATE HumanResources.EmployeePayHIstory
SET Rate = Rate +1
FROM HumanResources.EmployeePayHistory
IF (SELECT max (Rate) +1 FROM
HumanResources.EmployeePayHistory)>127
BREAK
ELSE
CONTINUE
END

How to use Constructs in Batches for Conditional Execution: SQL

SQL Server allows you to use programming constructs in the batches for conditional execution of statements. For example, you need to retrieve data based on a condition. If the condition is not satisfied, a message should be displayed.

The SQL Server allows you to use the following constructs to control the flow of statements:

• IF…..ELSE statement
• CASE statement
• WHILE statement

Using the IF….ELSE Statement

You can use the IF…..ELSE statement for conditional execution of SQL statements. A particular action is performed when the given condition on evaluates to TRUE and another action is performed when the given condition evaluates to FALSE.
The syntax of IF….ELSE statement is:

IF Boolean_expression
{sql_statement | statement_block}
ELSE
{sql_statement | statement_block}]
Where,

• Boolean_expression specifies the condition that evaluates to either TRUE or FALSE. Sql_statement specifies a T-SQL statement.
• Statement_block is a collection of T-SQL statements.

The following example retrieves the pay rate of an employee from the EmployeePayHistory table to a variable, @Rate. The value of the @Rate variable is compared with the value 15 by using the <(less than) comparison operator. Based on the condition, different messages are displayed.

DECLARE @Rate money
SELECT @Rate = Rate FROM HumanResources.EmployeeHistory
WHERE EmployeeID = 23
IF @Rate < 15
PRINT ‘Review of the rate is required’
ELSE
BEGIN
PRINT ‘Review of the rate is not required’
PRINT ‘Rate =’
PRINT @Rate
END
GO

In the preceding example, the IF statement checks if the rate variable is storing a value less than 15. If the result is true, the PRINT statement displays “Review of the rate is required” else it displays “Review of the rate is not required”. Further, the next PRN statement displays the value of the rate.

Consider another example, where a check is performed to see the existence of the sales department. If the Sales department exists, all the details are displayed otherwise, a user-defined message is displayed.

IF EXISTS (SELECT * FROM HumanResources.Department WHERE Name = ‘Sales’)
BEGIN
SELECT * FROM HumanResources.Department WHERE Name = ‘Sales’
END
ELSE
PRINT ‘Department details not available’
GO

How to Create Index using sql Query: Sql Server

Database programmer should create indexes on the most frequently queried column in a table. However, at times, you might need to create an index based on a combination of one or more columns. An index based on one or more columns is called a composite index. A composite index can be based on a maximum of 16 columns. However, you need to consider that indexes with less number of columns use less disk space and involve fewer resources when compared to indexes based on more columns.

To create an index you can use the CREATE INDEX statement. The syntax of the CREATE INDEX statement is:

CRATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON [{database_name.[schema_name]. | schema_name.}]
{table_or_view_name} (column [ASC | DESC] [, …n])
[INCLUDE (column_name [, …n])]
[WITH (<relational_index_option>[, …n])]
[ON {partition_cheme_name (column_name [, …n]) | filegroup_name |DEFAULT) ]
<relation_index_option> : : =
{PAD_INDEX = {ON | OFF}
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = {ON | OFF}
| IGNORE_DUP_KEY = {ON | OFF}
| STASTISTICS_NO_RCOMPUTE = {ON | OFF}
| DROP_EXISTING = {ON | OFF}
| ONLINE = {ON | OFF}

Where,

• UNIQUE crates an index where each row should contain a different index value. CLUSTERED specifies a clustered index where data is sorted on the index attribute. NONCLUSTERED specifies a nonclustered index that organizes data logically. The data is not sorted physically.
• Index_name specifies the name of the index.
• Table_name specifies the name of the table that contains the attributes on which the index is to be created.
• Column specifies the column or columns on which the index will be created.
• Column_name specifies the name of the column or columns on which the index would be created.
• ON partition_scheme_name ( column_name ) specifies the partition scheme the specifies the filegroups in which the pattitioned index will be mapped.
• ON filegroup_name specifies the filegroup on which index is created.
• ON DEFAULT specifies that the specified index will be created on the default filegroup.
• PAD_INDEX = { ON | OFF } specifies the index padding, which is OFF, by default.
• FILLFACTOR = 1 to 100 specifies a percentage that indicates how full the leaf level of each index page should become during index creation or rebuild. The default value is 0.
• SORT_IN_TEMPDB = { ON | OFF } specifies about storing temporary sort results in the tempdb.
• IGNORE_DUP_KEY = { ON | OFF } specifies whether a duplicate key value can be inserted or not…
• STATISTICS_NO_RECOMPUTE = { ON | OFF } specifies about recomputing the distribution statistics.
• DROP_EXISTING = { ON | OFF } specifies that the pre-existing clustered, nonclustered, or XML index is dropped and rebuilt.
• ONLINE = { ON | OFF } checks whether the underlying tables and associated indexes are available to query and modify the data during the index operation.

Developer can create online indexes only in the SQL Server 2005 Enterprise Edition.

Non-clustered index in sql
Guidelines to create indexes

How to Modify XML Data using Functions in SQL Server

Similar to any other type of data, programmer might also need to modify the XML data. To modify data, you can use the modify function provided by the XML data type of the SQL Server. The modify function specifies an XQuery expression and a statement that specifies the kind of modification that needs to be done.

This function allows you to perform the following modifications:

• Insert: Used to add nodes to XML in an XML column or variable. For example, the management of AdventureWorks wants to add another column specifying the type of customer, in the CustDetails table. The default value in the Type column should be ‘Credit’. To resolve this problem, the database developer of AdventureWorks will create the following query:
  
  UPDATE CusomtDetails SET Cust_Details.modify (‘ inser attribute Type{“Credit”} as first into (/Customer) [1]’)
  
• Replace: Used to update the XML data. For example, James Stephen, one of the customers of AdventureWorks, has decided to change his customer type from Credit to Cash. As a database developer, you can create the following query to reflect this change:
  
• Delete: Used to remove a node from the XML data. For example, the management of AdventureWorks has decided to remove the ‘City’ column from the customer details. You can write the following query to display the results:
  
  UPDATE CustomDetails SET Cust_Details.modify (‘delete (/Customer/@City) [1]’)

Retreive XML data using XQuery

How to Retrieve XML Data Using XQuery

In addition to FOR XML, SQL Server allows programmer to extract data stored in variables or columns with the XML data type by using XQuery. XQuery is a language that uses a set of statements and functions provided by the XML data type to extract data. As compared to the FOR XML clause of the SELECT statement, the XQuery statements allow you to extract specific parts of the XML data.

Each XQuery statement consists of two parts, prolog and body. In the prolog section, you declare the namespaces. In addition, schemas can be imported in the prolog. The body parts specifies the XML nodes to be retrieved. The XQuery language includes the following statements:

• For: Used to iterate through a set of nodes at the same level as in an XML document.
• Let: Used to declare variables and assign values.
• Order by: Used to specify a sequence.
• Where: Used to specify criteria for the data to be extracted.
• Return: Used to specify the XML returned from a statement.

The XQuery statements also use the following functions provided by the XML data type:

Query: Used to extract XML from an XML data type. The XML to be retrieved is bicycle is manufactured at AdventureWorks, it passes through a series of work centre locations. Each work centre location produces a different cycle component. Therefore, the number of production steps varies between different work centres.

To analyse the production process, the management of AdventureWorks needs to retrieve a list of the location IDs of all the work centers, which have more than four steps. You need to generate the list displaying the location ids in the ascending order of the steps included in the work centres.

To perform this task, the database developer can create the following query:

SELECT Instructons.query
(‘ declare namespace
ns=http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions:
for $work in /ns:root/ns:Location
where count(#work/ns:step) > 4
order by count ($work/ns:step)
return
count($work/ns:step)’) AS Result
FROM Production.ProductModel
WHERE Instructions IS NOT NULL

Value: Used to return a single value from an XML document. To extract a single value, you need to specify an XQuery expression that identifies a single node and a data type of the value to be retrieved.

For example, the management of AventureWorks, Ins. Wants a list containing the product model id, product name, machine hours, and labour hours. However, not all product have production instructions. As a database developer, you have stored this data in the XML format in the ProductModel table. You can create the following query to display the results:

SELECT ProductModelID, Name, Instructions.value (‘declare namespace ns=”http//schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductManuInstructions”;
(/ns:root/ns:Location/@LaborHours) [1]’, ‘float’)AS
LaborHours,
Instructions.value(declare namespace
Ns=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions”;
(/ns:root/ns:Location/@MachineHours) [1]’, ‘float’) AS MachineHours
FROM Production.ProductModel
WHERE Instructions IS NOT NULL

Exist: Used to check the existence of a node in an XML data. The function returns I if the specified node exists else it returns 0. For example, the management of AdventureWorks, wants the details of all the customers in the city ‘NJ’. The details of all the customers are stored in an XML format in the CustDetails table. You can use the following query to display the results:

SELECT Cust_ID, Cust_Details.exist
(‘Customer[@City=’NJ”]’) AS ‘True’ FROM CustDetails

Retrieve XML data from DataSet

How to Retrieve XML Data from a Database Table: SQL

At times, you need to retrieve the relational data from a table into the XML format for reporting purposes or to share the data across different applications. This involves extracting data from a table in the form of well-formed XML fragments. You can retrieve the XML data in the following ways:

• Using the FOR XML clause in the SELECT statement
• Using XQuery

Using the FOR XML Clause in the SELECT statement

SQL Server allows you to extract data from relational tables into an XML format by using the SELECT statement with the FOR XML clause. You can use the FOR XML clause to retrieve the XML data by using the following modes:

• RAW
• AUTO
• PATH
• EXPLICIT

Using the RAW Mode

The RAW mode is used to return an XML file with each row representing an XML element. The RAW mode transforms each row in the query result set into an XML element with the element name row. Each column value that is not NULL is mapped to an attribute with the same name as the column name.

The following statements display the details of employees with employee ID as 1 or 2:
SELECT EmployeeID, ContactID, LoginID, Title
FROM HumaneResources.Employee
WHERE EmployeeID=1 OR EmployeeID=2
FOR XML RAW

The preceding query displays the employee details in the following format:

<row EmployeeID=”1” ContactID=”1209” LoginID=”adventure-works/guy1” Title=”Production Technician – WC60” />
<row EmployeeID=”2” ContactID=”1030” LoginID=”adventure-works/kevin0” Title=”Marketing Assistant” />

Using the AUTO Mode

The AUTO mode is used to return query results as nested XML elements. Similar to the RAW mode, each column value that is not NULL is mapped to an attribute that is named after either the column name or the column alias. The element that these attributes belong to is named to the table that they belong to or the table alias that is used in the SELECT statement, as shown in the following query:

SELECT EmployeeID, ContactID, LoginID, Title
FROM HumanResources.Employee Employee
WHERE EmployeeID=1 OR EmployeeID=2
FOR XML AUTO

If the optional ELEMENTS directive is specified in the FOR XML clause, the columns listed in the SELECT clause are mapped to sub-elements, as shown in the following query:

SELECT EmployeeID, ContactID, LoginID, Title
FROM HumanResources.Employee Employee
WHERE EmployeeID=1 OR EmployeeID=2
FOR XML AUTO, ELEMENTS

Using the PATH Mode

The PATH mode is used to return specific values by indicating the column names for which you need to retrieve the data, as shown in the following query:

SELECT EmployeeID “@EmpID”,
FirstName “EmpName/First”,
MiddleName “EmpName/Middle”,
LastName “EmpName/Last”
FROM HumanResources.Employee e JOIN Person.Contact c
AND e.EmployeeID=1
FOR XML PATH

The preceding query displays the output in the following format:
<row EmpID=”1”>
<EmpName>
<First>Guy</First>
<Middle>R</Middle>
<Last>Gilbert</Last>
</Employee>
</row>

In the preceding result set, the EmployeeID column is mapped to the EmpID attribute with @ sign. The FirstName, MiddleName, and LastName columns are mapped as subelements of the EmpName element with the slash mark(/)

You can also use the optional ElementName argument with the PATH mode query to modify the name of the default row element, as shown in the following query:

SELECT EmployeeID “@EmpID”,
FirstName “EmpName/First”,
MiddleName “EmpName/Middle”,
LastName “EmpName/Last”
FROM HumanResources.Employee e JOIN Person.Contact c ON e.ContactID = c.ContactID
AND e.EmployeeID=1
FOR XML PATH (‘Employee’)

Using the EXPLICIT Mode

The EXPLICIT mode is used to return an XML file that obtains the format as specified in the SELECT statement. Separate SELECT statement can be combined with the UNION ALL statement to generate each level/element in the resulting XML output. Each of these SELECT statements requires the first two tags to be called Tag and Parent. The Parent element is used to control the nesting of elements. It contains the tag number of the parent element of the current element. The top-level element in the document should have the Parent value set to 0 or NULL.

For example, the managers of AdventureWorks want to access the information regarding products through their mobile devices. These devices cannot directly connect to the SQL Server, but can read the data provided in the XML format. Therefore, you need to convert the details of the products from the Product table into the XML document. To perform this task, you need to create an XML document with <Product> as the parent tag. The <Product> tag will contain ProductID as an attribute and <ProductName> and <Color> as child elements.

To perform this task, the database developer can create the following query:

SELECT 1 AS Tag,
NULL AS Parent,
ProductID AS [Product!1!ProductID],
Name AS [Product!1!ProductName!element],
Color AS [Product!1!Color!elementxsinil]
FROM Production.Product
FOR XML EXPLICIT

How to Store Typed XML Data in XML Columns: SQL

To store the typed XML data, programmer need to first register the schema associated with the data in the XML schema collection objects in the database. The XML schema collection is an object on the SQL Server that is used to save one or more XML schemas. You can create an XML schema collection object by using the following SQL statement:

CREATE XML SCHEMA COLLECTION <Name> as Expression
Where,

• Name specifies an identifier name with which the SQL Server will identify the schema collection.
• Expression specifies an XML value that contains one or more XML schema documents

For example, the customer details are associated with the following schema:
<?XML version=”1.0” ?>
<xsd:schema targetNamespace=”http//schemas.adventure-
Works.com/Customers” xmlns=”http//shemas.adventure-
Works.com/Customers” elementFormDefault=”qualified”
attributeFormDefault=” unqualified”
xmlns:xsd=”http://www.w3.org/2001/XMLSchema”?>
<xsd:element name =”Customer” type=”xsd:string” />
<xsd:attribute name=”City” type=”xsd:string” />
</xsd:schema>

You can use the following statements to register the preceding schema, named as CustomerSchemaCollection, with the database:

CREATE XML SCHEMA COLLECTION CustomerSchemaCollection AS n’<?xml version= “1.0” ?>
<xsd:schema targetNamespace=”http://schemas.adventure-
Works.com/Customers” xmlns=”http://schemas.adventure-
Works.com/Customers” elementFormDefault=”qualified”
attributeFormDefault=”unqualified”
xmlns:xsd=”http://www.w3.org/2001/XMLSchema”.>
<xsd:element name =”Customer” type=”xsd:string” />
<xsd:attribute name=”Name” type=”xsd:string” />
<xsd:attribute name=”City” type=”xsd:string” />
</xsd:schema>’

You can view information about the registered schemas in a database by querying the sys.XML_schema_collection catalog view, as shown in the following statement:
SELECT * FROM sys.XML_schema_collections

After registering the XML schema, you can use the schemas to validate typed XML values while inserting records into the tables. You need to specify this while creating a table that will store the XML data. In the preceding example, if you need to validate the customer details with the CustomerSchemaCollection schema, you need to create the CustDetails table by using the following statement:

CREATE TABLE CustDetails
(
CustID int,
CustDetails XML
)

You can insert data into this table by using the following statement:
INSERT INTO CustDetails VALUES (2, ‘<?xml version=”1.0”?> <CustomerName=”Abrahim Jones” City=”Selina” />’)

While executing the preceding statement, the SQL Server will validate the values for the CustDetails column against the CustomerSchemaCollection schema.