Creating a Database in SQL Mgt Studio 2012

Creating a Database is to occupy some space in physical drive for the records saving through the application. These saved records can easily be listed or modified as per user’s requirement. To perform operations like add/edit/delete on these records a person used to create some application having capability of doing so. In earlier article, we have installed SQL Server Management Studio 2012 following some easy steps. This time we will establish a connection with server and create a database EmpDb having only a single table Employee. Start Management studio through all programs or whatever shortcut you have on your system and follow these steps.

1. First screen will prompt you to establish a connection with server requiring some values as      
2.        
3.  below: Just write (LocalDb)\v11.0 because i have only local db installed in my system, you can change as per your server installation.
4. On the left side there are some options "Database, Security, Replication, Management" are pre-added. Right click on Database and select New Database, a window will show as below prompting database name:                                                                                                                
   
5. Enter database name "EmpDb" and click on Ok button, it will create a new database with none of tables, views. Now look out the server connection there is a new entry of your database as shown:                                                                                                                                              
   
6. Right click on tables and select New Table, it will show you editor having three columns name, data-types and nullable. Write some columns with their data-types, don’t forgot to make Id a primary key and Identity set to true. Identity property will auto-increment this columns according to data entry. Save this table and enter name “Employee”. You can see the columns and their data-types in the below image:                                                                                          
   

Your database have easily been created with a single table and some columns. In the next article we create an edmx file for this database to be used for MVC application and will perform operation on this database.

Installing SQL Server Mgt Studio 2012

Installing SQL Server is as simple as installing a software following a list of steps. SQL Server 2012 was released in April 2012 and because of its list of features and easiness to use made favorite among professionals and beginners. We will learn about to creating database in SQL Server Management studio after installing it in the system.

Microsoft SQL Server is a powerful and free data management tool, designed for easy development and utilities including:

• LocalDB (MSI installer) LocalDB is a lightweight version of Express that has all its programmability features. It can be bundled with Application and Database Development tools like Visual Studio and or embedded with an application that needs local databases.
• Express (Containing only the database engine) Use this if you need to accept remote connections or administer remotely.
• Express with Tools (with LocalDB) Choose either LocalDB or Express depending on your needs above.
• SQL Server Management Studio Express (Tools only) This does not contain the database, but only the tools to manage SQL Server instances, including LocalDB, SQL Express, SQL Azure, etc. Use this if you already have the database and only need the management tools.
• Express with Advanced Services (contains the database engine, Express Tools, Reporting Services, and Full Text Search) This is a larger download than "with Tools" as it also includes both Full Text Search and Reporting Services.

Here are the steps to installing SQL Server Management studio 2012:

1. Download the executable file from the Microsoft official site. Double click on the .exe file and "SQL Server installation center" window will open.                                                    
   
2. Select first option "New SQL server stand-alone installation" and it will check all the support rules with a progress bar.                                                                                                  
   
3. After completing rule check it will opt for license terms that must accept by you for installation. User may also select the option to send feature data to Microsoft or just ignore this checkbox.                                                                                                                         
   
4. It will then install setup files used for preparing the installation into your system showing progress to user. On this step if your system is connected to internet connection then any update available will also be installed with this installation.             
   
5. Next step will opt for feature selection to be installed as shown in the image. Some features must be installed and those features are only readable as LocalDB and Connectivity tools                                                                                                                              
   
6. User can help Microsoft to SQL server features and services with selecting a checkbox of Error reporting. It is optional.                                                                                                    
   
7. Important step in which the installation process continues with copying all the required files on the system. This step takes more time because of copying required files.                                                                                                                                                       
   
8. The last step of installation to confirm about installation have successfully completed or having some errors. Successfully completed window will show with list of all the features installed with this installation.                                                                                        
   

Completing these steps you can easily access SQL Server management studio to create database. We will create database with some tables in next article.

Data Definition Language (DDL) Triggers in SQL

A DDL trigger is fired in response to DDL statements, such as CREATE TABLE or ALTER TABLE. DDL triggers can be used to perform administrative tasks, such as database auditing.

Database auditing helps in monitoring the DDL operations on a database. DDL operation can include operations such as creation of a table or view, or modifications of a table or procedure. Consider an example, where you want the database administrator to be notified whenever a table is created in the Master Database. For this purpose, you can create a DDL trigger.

Depending on the way in which triggers are fired, they are categorized as:

After Triggers

The after trigger can be created on any table for the insert, update or delete operation just like other triggers. The main difference in the functionality of an after trigger is that it is fired after the execution of the DML operation for which it has been defined. The after trigger is executed when all the constraints and triggers defined on the table are successfully executed.

By default, if more than one after trigger is created on a table is for a DML operation such as insert, update, or delete, then the sequence of execution is the order in which they were created.
For example, the EmpSalary table stores the salary and tax details for all the employees in an organization. You need to ensure that after the salary details of an employee are updated in the EmpSalary table, the tax details are also recalculated and updated. In such a scenario, you can implement an after trigger to update the tax details when the salary details are updated.

Instead of Triggers

The instead of triggers can be primarily used to perform an action, such as a DML operation on another table or view. This type of trigger can be created on both a table as well as a view.

An instead of trigger can be used for the following actions:

• Ignoring parts of a batch.
• Not processing a part of a batch and logging the problem rows.
• Taking an alternative action when an error condition is encountered.

For example, if a view is created with multiple columns from two or more tables, then an insert operation on the view is only possible if the primary key fields from all the base tables are used in the query. Alternatively, if you use an instead of trigger, you can insert data in the base tables individually. This makes the view logically updateable.

You can even create an Instead of trigger to restrict deletion in a master table. For example, you can display a message “Master record cannot be deleted” if a delete statement is executed on the Employee table of the AdventureWorks database.

Unlike after triggers, you cannot, create more than one Instead of trigger for a DML operation on the same table or view.

Nested Triggers

Nested triggers are fired due to actions of other triggers. For example, you delete a row from TableA. A trigger on TableA deletes rows from TableB. Because you are deleting rows from TableB, a trigger is executed on TableB to record the deleted rows.

Recursive Triggers

Recursive triggers are a special case of nested triggers. Unlike nested triggers, support for recursive triggers is at the database level. As the name implies, a recursive trigger eventually calls itself. There are two types of recursive triggers, Direct and Indirect.

Direct Recursive Trigger

A direct trigger is a trigger that performs the same operation (insert, update, or delete) on the same table causing the trigger to fire itself again.

Indirect Recursive Trigger

An indirect trigger is a trigger that fires a trigger on another table and eventually the nested trigger ends up firing the first trigger again. For instance, an UPDATE on TableA fires a trigger that in turn fires an update on TableB. The update on TableB fires another trigger that performs an update on TableC. TableC has a trigger that causes an update on TableA again. The update trigger of TableA is fired again.

Execute Batches multiple times using Stored Procedures in SQL

Batches are temporary in nature. To execute a batch more than once, you need to recreate SQL statements and submit them to the server. This leads to an increase in the overhead, as the server needs to compile and create the execution plan for these statements again. Therefore, if you need to execute a batch multiple times, you can save it within a stored procedure. A stored procedure is a precompiled object stored in the database.

Stored procedures can invoke the Data Definition Language (DDL) and Data Manipulation Language (DML) statements and can return values. If you need to assign values to the variables declared in the procedures at the run time, you can pass parameters while executing them. You can also execute a procedure from another procedure. This helps in using the functionality of the called procedure within the calling procedure.

Creating Stored Procedures

You can create a stored procedure by using the CREATE PROCEDURE statement. The syntax of the CREATE PROCEDURE statement is:

CREATE PROCEDURE proc_name
AS
BEGIN
Sql_statement1
Sql_statement2
END

Where Proc_name specifies the name of the stored procedure.

The following example create a stored procedure to view the department names from the Department table:

CREATE PROCEDURE prcDept
AS
BEGIN
SELECT Name FROM HumanResources.Department
END

When the CREATE PROCEDURE statement is executed, the server compiles the procedure and saves it as a database object. The procedure is then available for various applications to execute. The process of compiling a stored procedure involves the following steps:

• The procedure is compiled and its components are broken into various pieces. This process is known as parsing.
• The existence of the referred objects, such as tables and views, are checked. This process is known as resolving.
• The name of the procedure is stored in the sysobjects table and the code that creates the stored procedure is stored in the syscomments table.
• The procedure is compiled and a blueprint for how the query will run is created. This blueprint is specified as execution plan. The execution plan is saved in the procedure cache.
• When the procedure is executed for the first time. The execution plan will be read and fully optimized and then run. The net time the procedure is executed in the same session, it will be read directly from the cache. This increases performance, as there is no repeated compilation.

After creating the stored procedure, you can view the code of the procedure by using the sp_helptext command.

Implementing Triggers and its Types in SQL

In a relational database, data in a table is related to other tables. Therefore, while manipulating data in one table, you need to verify and validate its effect on data in the related tables. In addition, you might need to manipulate data in a table after inserting or updating data in another table.

You also need to ensure that if an error occurs while updating the data in a table, the changes are reverted. This helps in maintaining data integrity. The SQL Server allows you to implement triggers and transactions to maintain data integrity.

This article explains different types of triggers that can be created in SQL Server. Next, we will discusses how to implement triggers to enforce data integrity. Further, we will discuss about how to implement transactions.

Implement Triggers

At times, while performing data manipulation on a database object, you might also need to perform another manipulation on another object. For example, in an organization, the employees use the Online Leave Approval system to apply for leaves. When an employee applies for a leave, the leave details are stored in the Leave-Details table. In addition, a new record is added to the Leaves-For-Approval table. When the supervisors log on to the system, all the leaves pending for their approval are retrieved from the Leaves-For-Approval table and displayed to them.

To perform such operations, the SQL Server allows you to implement triggers. A trigger is a block of code that constitutes a set of T-SQL statements activated in response to certain actions, such as insert or delete. Triggers are used to ensure data integrity before or after performing data manipulations.

Before you implement a trigger, it is important to know the different types of triggers that can be created by using SQL Server.

Identifying Types of Triggers

In the SQL Server, various kinds of triggers can be used for different types of data manipulation operations. The SQL Server supports the following types of triggers:

Data Modification Language (DML) triggers

A DML trigger is fired when data in the underlying table is affected by DML statements, such as INSERT, UPDATE, or DELETE. These triggers help in maintaining consistent, reliable, and correct data in tables. They enable the performance of complex action and cascade these actions to other dependent tables. Cascading is the process of reflecting the changes made in a table in the other related tables.

Data Definition Language (DDL) triggers

A DDL trigger is fired in response to DDL statements, such as CREATE TABLE or ALTER TABLE. DDL triggers can be used to perform administrative tasks, such as database auditing.

Creating Table-Valued Functions in SQL

A table-valued function returns a table as an output, which can be derived as a part of a SELECT statement. Table-valued function return the output as a table data type. The table data is a special data type used to store a set of rows, which return the result set of a table-valued function. Table-valued functions are of two types:

Inline Table-Valued Function

An inline table-valued function returns a variable of a table data type from the result set of a single SELECT statement. An inline function does not contain a function body within the BEGIN and END statements.

Consider an example where the inline table-valued function, fx_Department_GName, accepts a group name as parameter and returns the details of the departments that belong to the group from the Department table. You can create the function by using the following statement:

CREATE FUNCTION fx_Department_GName ( @GrName nvarchar (20) )
RETURNS table
AS
RETURN (
SELECT *
FROM HumanResources.Department
WHERE GroupName=@GrName
)
GO

You can use the following statement to execute the fx_Department_Gname function with a specified argument:

SELECT * FROM fx_Department_GName (‘Manufacturing’)

The preceding statement will return a result set having the group name ‘Manufacturing’.

Consider another example of an inline function that accepts rate a a parameter and returns all the records that have a rate value greater than the parameter value:

CREATE FUNCTION HumanResources.Emp_Pay (@Rate int)
RETURNS table
AS
RETURN (
SELECT e.EmployeeID, e.Title, er.Rate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS er
ON e.EmployeeID=er.EmployeeID WHERE er.Rate<@Rate
)
GO
The preceding function will return a result set that displays all the records of the employees who have the pay rate greater that the parameter.

Multistatement Table-Valued Function

A Multistatement table-valued function uses multiple statements to build the table that is returned to the calling statement. The function body contains a BEGIN…END block, which holds a series of T-SQL statements to build and insert rows into a temporary table. The temporary table is returned in the result set and is created based on the specification mentioned in the function.

Consider an example where the Multistatement table-valued function, PayRate, is created to return a set of records from the EmployeePayHistory table by using the following statements:

CREATE FUNCTION PayRate (@rate money)
RETURNS @table TABLE
(EmployeeID int NOT NULL,
RateChangeDate datetime NOT NULL,
Rate money NOT NULL,
PayFrequency tinyint NOT NULL,
modifiedDate datatime NOT NULL)
AS
BEGIN
INSERT @table
SELECT *
FROM HumanResources.EmployeePayHistory
WHERE Rate > @rate
RETURN
END

The function returns a result set in from of a temporary table, @table, created within the function. You can execute the function by using the following statement:

SELECT * FROM PayRate (45)

Depending on the result set returned by a function can be categorized as deterministic or nondeterministic. Deterministic functions always return the same result whenever they are called with a specific set of input values. However, nondeterministic function may return different results each time they are called with a specific set of input values.

An example of a deterministic function is date add, which returns the same result for any given set of argument values for its three parameters. Get date is a nondeterministic function because it is always invoked without any argument, but the return value changes on every execution.

Creating Scalar functions in SQL

In earlier article we have discussed about user defined functions that have the limited scope in sql programming in compare to stored procedures. User defined function have two types i.e. scalar function and table-valued function. In this article we will discuss about scalar functions.

Scalar function accept a single parameter and return a single data value of the type specified in the RETURNS clause. A scalar function can return any data type except text, ntext, image, cursor, and timestamp. Some scalar functions, such and current_timestamp, do not require any arguments.

A function contains a series of T-SQL statements defined in a BEGIN…END block of the function body that returns a single value.

Consider an example of a scalar function that calculates the monthly salary of the employees accepting the pay rate as an input and returning a single value after multiplying the value with the number of hours and number of days:

CREATE FUNCTION HumanResources.MonthlySal (@PayRate float)
RETURN float
AS
BEGIN
RETURN (@PayRate * 8 * 30)
END

You can execute this function by using the following statements:

DECLARE @PayRate float
SET @PayRate = HumanResources.MonthlySal (12.25)
PRINT @PayRate

In the preceding code, @PayRate is a variable that will store a value returned by the MonthlySal function.

How to Implement User Defined Function in SQL

Similar to the stored procedures, you can also create functions to store a set of T-SQL statements permanently. These functions are also referred to as user-defined functions (UDFs). A UDF is a database object that contains a set of T-SQL statements, accepts parameters, performs an action, and returns the result of that action as a value. The return value can either be a single scalar value or a result set.

UDFs have a limited scope as compared to stored procedures. You can create functions in situations when you need to implement a programming logic that does not involve any permanent changes to the database objects outside the function. For example, you cannot modify a database table from a function.

UDFs are of different types: scalar functions and table-valued function. As a database developer, it is important for you to learn to create and manage different types of UDFs.

Creating UDFs

A UDF contains the following components:

• Function name with optional schema/owner name
• Input parameter name and data type
• Options applicable to the input parameter
• Return parameter data type and optional name
• Options applicable to the return parameter
• One or more T-SQL statements

To create a function, you can use th CREATE FUNCTION statement. The syntax of the CREATE FUNCTION statement is:

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [AS ] [ type_schema_name. ]
Parameter_data_type] }
[ = default ] }
[, …n ]
]
)
RETURNS return_data_type
[WITH <function_option> [ , . . .n ] ]
[ AS ]
BEGIN
Function_body
RETURN expression
END
[;]
Where,

• Schema_name is the name of the schema to which the UDF belongs.
• Function_name is the name of the UDF. Function names must comply with the rules for identifiers and must be unique within the database and to its schema.
• @parameter_name is a parameter in the UDF. One or more parameters can be declared.
• [type_schema_name.] parameter_data_type is the data type of the parameter, and optionally the schema to which it belongs.
• [=default ] is a default value for the parameter.
• Return_data_type is the return value of a scalar user-defined function.

Calling a Procedure from another Procedure: SQL

At times, you might need to use the values returned by a procedure in another procedure. For this, you can execute or call one procedure from within another procedure.

A procedure that calls or executes another procedure is known as the calling procedure, and the procedure that is called or executed by the calling procedure is termed as the called procedure. You can also execute a procedure from another procedure if you need to use the functionality provide by one into another.

Consider the previous example where the prcGetEmployeeDetail procedure returns the employee details for a given employee ID. You can create the prcDisplayEmployeeStatus procedure, which accepts the employee ID of an employee as input and displays the department name and shift ID where the employee is working along with the manager ID and the title employee. To perform this task, you need to call the prcGetEmployeeDetail procedure from the prcDisplayEmployeeStatus procedure, as shown in the following statement:

CREATE PROCEDURE prcDisplayEmployeeStatus @EmpId int
AS
BEGIN
DECLARE @DepName char (50)
DECLARE @ShiftId int
DECLARE @ReturnValue int
EXEC @ReturnValue = prcGetEmployeeDetail @EmpId,
@ DepName OUTPUT,
@ ShiftId OUTPUT
IF (@ReturnValue = 0)
BEGIN
PRINT ‘The details of an employee with ID: ‘ + convert (char (10), @EmpId)
PRINT ‘shift ID: ‘ + convert ( char (1), @shiftId)
SELECT ManagerID, Title FROM HumanResources.Employee
WHERE EmployeeID = @EmpID
END
ELSE
PRINT ‘No records found for the given employee’
END

To execute the preceding code, you need to execute the following statement:
EXEC prcDisplayEmployeeStatus 2

SQL Server provides a function, @@ROWCOUNT, which return the number of rows affected by the last statement. You can use this statement in the IF construct to check the result of the last statement that executed.

How to Return values from Stored Procedure: SQL

Similar to providing input values to the procedures at run time, you can also return values as output from the procedures. The values can be returned to the calling application through output parameters. To specify a parameter as the output parameter, you can use the OUTPUT keyword.

The OUTPUT keyword has to be specified in both the CREATE PROCEDURE and the EXECUTE statement. If the OUTPUT keyword is omitted, the procedure will be executed but will not return any value.

The syntax of the declaring an output parameter using the OUTPUT keyword is:

CREATE PROCEDURE procedure_name
[
{ @parameter data_type} [OUTPUT]
]
AS
Sql_statement […n]

• @parameter data_type [OUTPUT] allows the stored procedure to pass a data value to the calling procedure. If the OUTPUT keyword is not used, then the parameter is treated as an input parameter.

You can also return values from the stored procedure by using the RETURN statement. The RETURN statement allows the stored procedure to return only an integer value to the calling application. The syntax of the RETURN statement is:

RETURN value
Where,

• Value is any integer. If a value is not specified, then the stored procedure returns a default value of 0 to specify failure and 1 to specify success.

Consider an example. You need to display the details of an employee whose employee ID has been provided as an input. For this, you need to create a procedure prcGetEmployeeDetail thet will accept employee ID as input and will return the department name and ID of the shift in which the employee works. You can create the procedure, as shown in the following statement:

CREATE PROCEDURE prcGetEmployeeDetail @EmpID int, @DepName char (50) OUTPUT, @ShiftID int OUTPUT
AS
BEGIN
IF EXISTS (SELECT * FROM HumanResources.Employee WHERE EmployeeID = @EmpID)
BEGIN
SELECT @DepName = d. Name, @Shiftid = h.ShiftID
FROM HumanResourcess.Department d JOIN
HumanResources.EmployeeDepartmentHistory h
ON d.DepartID = h.DepartmentID
WHERE EmployeeID = @EmpId AND h.Enddate IS NULL RETURN 0
END
ELSE
RETURN 1
END

The preceding procedure accepts the employee ID as an input parameter and returns the department name and shift ID as output parameters. The procedure first checks the existence of the given employee ID. Id it exists, the procedure returns and integer value 0 along with the required details.

How to Create Parameterized Stored Procedure: SQL

At times, you need to execute a procedure for different values of a variable that are provided at run time. For this, you can create a parameterized stored procedure. Parameters are used to pass values to the stored procedure during run time. These values can be passed by using standard variables.

The parameter that passes the values is defined as input parameter. A stored procedure has the capability of using a maximum of 2100 parameters. Each parameter has a name, data type, direction, and a default value.

The following example creates a stored procedure displaying the employee ID, the login ID, and title of the employees that have the same title provided as an input during

Execution:

CREATE PROC prcListEmployee @title char (50)
AS
BEGIN
PRINT ‘List of Employees’
SELECT EmployeeID, LoginID, Title
FROM HumanResources.Employee
WHERE Title = @title
END

Execute the stored procedure, prcListEmployee, by using the following statement:

EXECUTE prcListEmployee ‘Tool Designer’

While executing the stored procedures, you can also provide the values for the parameters by explicity specifying the name and value of the parameter. In the previous example, you can also pass the parameter value by using the name of variable, as shown in the following SQL statement:

EXECUTE prcListEmployee @title = ‘Tool Designer’

Guidelines to Create and Execute Stored Procedure: SQL

To create stored procedure database developer have to keep some points in mind. The following points need to be considered before creating a stored procedure:

• You cannot combine the CREATE PROCEDURE statement with other SQL statements in a single batch.
• You must have the CREATE PROCEDURE permission to create a procedure in the database and the ALTER permission on the schema, where the procedure is being created.
• You can create a stored procedure only in the current database.
• After creating a stored procedure, you can execute the procedure. You can also alter the procedure definition or drop it, if not required.

Executing a Stored Procedure

A procedure can be executed by using the EXEC PROCEDURE statement. The syntax of the EXEC PROCEDURE statement is:

EXEC | EXECUTE PROCEDURE proc_name AS [ { LOGIN|USER} = ‘name’]
Where,

• Proc_name, is the name of the procedure you need to execute.
• LOGIN|USER specifies the name of the login of another user on the same database server or another user in the same database that you need to impersonate. While executing a procedure you can impersonate the rights of another user or ligin to execute the procedure. This allows a user who does not have permission to execute a procedure to execute it using the permissions assigned to other users.

Consider an example. Robert has created a stored procedure named DispEmpDetail that displays the details of the employees. Kim needs to execute the procedure but does not have the execution rights. In such a case, Kim can use the rights of Robert to execute. For this, Kim needs to impersonate Robert.

You can execute the stored procedure, prcDept, as shown in the following statement:
EXEC PROCEDURE prcDept

Altering Stored Procedure

A stored procedure can be modified by using the ALTER PROCEDURE statement. The syntax of the ALTER PROCEDURE statement is:

ALTER PROCEDURE proc_name

You can alter the stored procedure, prcDept, as followis:

ALTER PROCEDURE prcDept
AS
BEGIN
SELECT DepartmentID, Name FROM HumanResources.Department
END
In the preceding code, the DepartmentID attribute will be displayed along with the department name.

Dropping a Stored Procedure

You can drop a stored procedure from the database by using the DROP PROCEDURE statement. The syntax of the DROP PROCEDURE statement is:

DROP PROCEDURE proc_name

You cannot retrieve a procedure once it is dropped. You can drop the prcDept stored procedure by using the following statement:

DROP PROCEDURE prcDept

Handling Errors and Exceptions using RAISERROR: SQL

A REISEROR statement is used to return messages to the business applications that are executing the SQL statements. This statement uses the same format as a system error or warning message generated by the database engine. Consider an example of an application that is executing a batch. If an error occurs while executing this batch, an error message will be raised and sent to the application. The application in turn will include the code to handle the error.

You can also return user-defined error messages by using the RAISERROR statement. A REISERROR severity of 1; to 19 executed in the TRY block causes the control to be transferred to the associated CATCH block.

Consider the following example of the AdventureWorks database that stores the details of the shift in which the employees work. You need to update the Shift table to update the time of a shift. While updating the shift details, you need to ensure that the difference between the start time and the end time is eight hours. If it is less than eight hours, an error is raised, and the update process is stopped.

BEGIN TRY
DECLARE @Start datetime
DECLARE @End datetime
DECLARE @Date_diff int
SELECT @Start = ‘1900-01-01 23:00:00.000’, @End = ‘1900-01-02 06:00:00.000’
SELECT @Date_diff = datediff (hh, @Start, @End)
IF (@Date_diff != 8)
 RAISERROR (‘Error Raised’, 16, 1)
ELSE
BEGIN
UPDATE HumanResources.Shift
SET StartTime = @Start, EndTime = @End
WHERE ShiftID = 3
END
END TRY
BEGIN CATCH
PRINT, ‘The difference between the Start and End time should be 8 hours’
END CATCH
GO

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

Implementing Batches in SQL Server and Guidelines

As a database developer, you might need to execute more than one SQL statement to perform a task. For example, when a new employee joins AdventureWorks, Inc., you need to insert the employee details in the database. The details of the employees are stored in more than one table. Therefore, you need to execute an insert statement into store the details in each table. In such a case, you can send all the SQL statements together to the SQL Server to be executed as a unit. This helps in reducing the network traffic.

Creating Batches

A batch is a group of SQL statements submitted together to the SQL Server for execution. While executing batches, the SQL Server compiles the statements of a batch into a single executable unit called an execution plan. This helps in saving execution time.

Consider an example. You have to execute 10 statements and you are executing them one by one by sending 10 requests. This process takes time if your queries are in queue. All statements together in a batch, then the execution process becomes faster as all the statements are sent to the server together.

To create a batch, you can write multiple SQL statements followed by the keyword GO at the end, as shown in the following listing:
<T-SQL Statement1>
<T-SQL Statement2>
<T-SQL Statement3>
…. . .
GO

GO is a command that specifies the end of the batch and sends the SQL statements to an instance of the SQL Server.

Consider an example. If you want to store the details of new employees in the Adventure Works database, you can create the following batch:

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’, 19978, ‘robert1’, 16, ‘Tool Designer’,
‘1972-05-15’, ‘S’, ‘M’, ‘1996-07-31’, 0, 16, 20, 1, newid ( ) ,

When a batch is submitted to the SQL Server, it is compiled to create an execution plan. If any compilation error occurs, such as a syntax error, the execution plan is not created. Therefore, none of the statements in the batch is executed. However, after the execution plan is created, if a run-time error occurs, the execution of the batch stops. In such a case, the statements executed before the statement that encountered the run-time error are not affected.

Using Variables

While creating batches, you might need to save some values temporarily during the execution time. For example, you might need to store some intermediate values of calculations. To store the intermediate values, you can declare variables and assign values to them. You can declare a variable by using the DECLARE statement. The syntax of the DECLARE statement is:

DECLARE @variable_name data_type

Variables that are declare in a batch and can be used in any statement inside the batch are called local variables.
The following code declares a variable, @Rate, and assigns the maximum value of the Rate column from the EmployeePayHistory table to the variable:

DECLARE @Rate int
SELECT @Rate = max (Rate)
FROM HumanResources.EmployeePayHistory
GO
In the preceding example, the @Rate variable is declardd and used to store the maximum value of the Rate column. The max aggregate function is used to retrieve the maximum pay rate from the EmployeePayHistory table. The GO keyword is used to send all the statements together to the SQL Server.

Displaying User-Defined Messages

At times, you need to display user-defined messages or values of variables when the batch is executed. For this, you can use the PRINT statement, as shown in the following batch.
The following code displays the value of the rate variable by using the PRINT statement.

DECLARE @Rate int
SELECT @Rate = max (Rate)
FROM HumanResources.EmployeePayHistory
PRINT @Rate
GO
You can also use comment entries in batches to write a description of the code. This will help understand the purpose of the code. A comment entry can be written in two ways:

• Multiple line comment entries enclosed within /* and */
• Single line comment entry starting with – (double hyphens).

Guidelines to Create Batches

While crating batches, you need to consider the following guidelines:

• You cannot combine statements, such as CREATE DEFAULT, CRATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW with other statements while creating a batch. Any statement that follows the create statement is interpreted as part of the definition.
• You can use the EXECUTE statement in a batch when it is not the first statement of the batch, otherwise the EXECUTE statement works implicitly.

In addition, you need to consider the following restrictions:

• You cannot bind rules and defaults to columns and use them in the same batch.
• You cannot define and use the CHECK constraint in the same batch.
• You cannot drop objects and recreate them in the same batch.
• You cannot alter a table by adding a column and then refer to the new columns in the batch created earlier.

How to Perform Searching Data by Using a Full-Text Search

After the full-text index has been created on a table, you can query the table by using the full-text predicates. The full-text predicates are used to specify how the search string should be searched in the table.
Predicates can be used to enhance the search so that the result have so focus on the search criteria. Sql programming have a list of predicates to be used.

The following predicates can be used while performing the full-text search:

FREETEXT: When the search criteria are given, FREETEXT searches for any variation of a word or a group of words given in the search column. FREETEXT is used for the prefix searches.
Considering the previous scenario of the bike racing competition, you can use the FREETEXT predicate to obtain the desired output, as shown in the following statement:

SELECT Description FROM Production.ProductDescription WHERE FREETEXT (Description, ‘race winners’)

CONTAINS: This predicate is used in queries when you want to search for a specific phrase or for the exact match. It also searches for the proximity of words within a text. For example, you can use the following statement to search for the words ‘Ride’ near the word ‘Bike’ in the ProductDescription table:

SELECT Description FROM Production.ProductDescription
WHERE CONTAINS (Desctiption, ‘ride NEAR bike’)

Populating the Full-Text Index in Search: SQL Server

After creating the full-text index, you need to populate it with the data in the columns enabled for full-text support. The SQL Server full-text search engine populates the full-text index through a process called population. Population involves filling the index with words and their location in the data page. When a full-text index is created, it is populated by default. In addition, the SQL Server automatically updates the full-text index as the data is modified in the associated tables.

However, the SQL Server does not keep a list of changes made to the indexed data when the CHANGE_TRACKING option is off. This option is specified while creating the full-text index by using the CREATE FULLTEXT INDEX statement.

If you do not want the full-text index to be populated when it is created using the CREATE FULLTEXT INDEX statement, then you must specify NO POPULATION along with the CHANGE TRACKING OFF option. To populate the index, you need to execute the ALTER FULLTEXT INDEX command along with the START FULL, INCREMENTAL, or UPDATE POPULATION clause.

For example, to create an empty full-text index on the ProductDescription table, you can execute the following statement:

CREATE FULLTEXT INDEX ON Production. ProductionDescription (Description)
KEY INDEX PK_ProductDescription_ProductDescriptionID
WITH CHANGE_TRACKING OFF, NO POPULATION

To populate the index you need to execute the following statement:
ALTER FULLTEXT INDEX ON Production. ProductDescription START FULL POPULATION

The preceding statement will populate the full-text index created on the ProductDesctiption table.

Similar to regular SQL indexes, full-text indexes can also be updated automatically as the data is modified in the associated tables. This repopulation can be time-consuming and adversely affect the usage of resources of the database server during periods of high database activity. Therefore, it is better to schedule repopulation of full-text indexes during periods of low database activity. You can specify the following types of full-text index population methods to repopulate the index:

Full Population

You can use this method when you need to populate the full-text catalog or the full-text index for the first time. After that, you can the maintain the indexes by using change tracking or incremental populations.
During a full population of a full-text catalo, index entries are built for all the rows in all the tables covered by the catalog. If a full population is requested for a table, index entries are built for all the rows in that table.

Change Tracking-Based Population

The SQL Server maintains a record of the rows that have been modified in a table set up for full-text indexing. These changes are propagated to the full-text index.

Incremental Timestamp-Based Population

The incremental population method updates the full-text index with the data that has been changed since the last time the index was refreshed. For an incremental population refresh to work, the indexed table must have a column of the timestamp data type. If a table does not have a column of the timestamp data type, then only a full population refresh can be done.

Configuring Full-Text Search in SQL Server

The full-text query feature in the SQL Server enables users to search for a wide range of text in the SQL tables. Consider an example. The sales management team of AdventureWorks, Inc. makes frequent searches on the ProductDescription table to develop marketing strategies. The search is based on the data stored in the Description column of the table.

A bike racing competition is scheduled to begin in Texas. The sales manager of AdventureWorks wants to see the details of all the bikes that are related to racing, so that a marketing strategy can be designed to increase the sale of these bikes. Specifically, he wants a list of all the bikes that have the keyword ‘race winner’ in the description.

As the data is large, the search query takes a long time to retrieve data from the table. In this scenario, you can apply a full-text index on the Description column of the ProductDescription table to improve the speed of searching.

To retrieve the required details by using full-text search, you need to configure full-text search on the database. For this, you need to perform the following tasks:

• Enable the full-text search in the database.
• Create a full-text catalog.
• Create a unique index.
• Create a full-text index.
• Populate the full-text index.

Enabling the Full-Text Search in the Database

Before using the full text search feature of the SQL Server, you need to enable the database using the following statement:
USE AdventureWorks
GO
Sp_fulltext_database enable
GO

Creating a Full-Text Catalog

A full-text catalog serves as a container to store full-text indexes. After enabling the full text search, you need to create a full-text catalog. A full-text catalog is a container that contains full-text indexes. A full-text catalog may have multiple full-text indexes. You can create a full-text catalog by using the following command:
CREATE FULLTEXT CATALOG cat1 AS DEFAULT

Creating a Unique Index

After creating the full-text catalog, you need to identify a unique index on the table. This unique index will be mapped to the values in the full-text index. You can use an existing unique index defined on the table, or create a new one. For example, you can create a unique index on the Production.ProductDescription table, as shown in the following statement:

CREATE UNIQUE INDEX Ix_Desc ON Production.ProductDescription
(ProductDescriptionID)

Creating a Full-Text Index

After you have created the full-text catalog and a unique index, you can create a full-text index on the table. A full-text index stores information about significant words and their location within a given column. You can use this information to compute full-text queries that search for rows with particular words or combinations of words. Full-text indexes can be crated on the base tables but not on the views or the system tables.

There are certain words that are used often and may hinder a query. These words are called noise words and are excluded from the search string. For example, if you search string is “Who is the governor of California”, a full-text search will not look for words, such as ‘is’ and ‘the’. Some noise words are a, an, the, and are.

Based on the preceding scenario, you can create a full-text index on the ProductionDescription table. This index is based on the lx_desc index created earlier on the Description column of the table.
Note: You can also create full-text index in the Object Explorer window by right-clicking the table, on which you need to create the full-text index, and selecting Full-Text index-Define Full-Text Index.