SQL – Stored Procedures and Functions

Creating a stored procedure

A stored procedure is a saved collection of Transact-SQL statements that can take and return user-supplied parameters.
Stored procedures can be found in SQL Server under the database tree view > Programability > Stored Procedure.
You can create one using the Query Analyser or by right clicking on “Stored Procedures” folder in SQL and selecting “New Stored Procedure”.

Here is a sample

CREATE PROCEDURE tblProducts_GetAllForType
	-- Add the parameters for the stored procedure here
	@Type int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * FROM tblProducts WHERE [Type]=@Type
END
GO

This can be called for example with the following statement:

exec dbo.tblProducts_GetAllForType 1

Creating a function

CREATE FUNCTION creates a user-defined function in SQL Server.
A user-defined function is a Transact-SQL that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table. Use this statement to create a reusable routine that can be used in these ways:

  • In Transact-SQL statements such as SELECT
  • In applications calling the function
  • In the definition of another user-defined function
  • To parameterize a view or improve the functionality of an indexed view
  • To define a column in a table
  • To define a CHECK constraint on a column
  • To replace a stored procedure

Example:

CREATE FUNCTION Add_two_Numbers
(
	-- Add the parameters for the function here
	@number1 int, @number2 int
)
RETURNS int
AS
BEGIN
	-- Declare the return variable here
	DECLARE @result int

	-- Add the T-SQL statements to compute the return value here
	SET @result = @number1 + @number2

	-- Return the result of the function
	RETURN @result

END
GO


This function can be executed like this:

SELECT dbo.Add_Two_Numbers (1,5) as Total

Will display 6

SQL Fundamentals

Database Tables

A database most often contains one or more tables. Each table is identified by a name (e.g. “Customers” or “Orders”). Tables contain records (rows) with data.

Below is an example of a table called “Persons”:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
2 Bob Builder The Main Road 23 Corby
3 Peter Pan Neverland Ohio

The table above contains three records (one for each person) and five columns (P_Id, First_Name, Last_Name, Address, and City).


SQL Statements

Most of the actions you need to perform on a database are done with SQL statements.

The following SQL statement will select all the records in the “Persons” table:

SELECT * FROM Persons

In this tutorial we will teach you all about the different SQL statements.


Keep in Mind That…

  • SQL is not case sensitive

Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement.

Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.

We are using MS Access and SQL Server 2000 and we do not have to put a semicolon after each SQL statement, but some database programs force you to use it.


SQL DML and DDL

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).

The query and update commands form the DML part of SQL:

  • SELECT – extracts data from a database
  • UPDATE – updates data in a database
  • DELETE – deletes data from a database
  • INSERT INTO – inserts new data into a database

The DDL part of SQL permits database tables to be created or deleted. It also define indexes (keys), specify links between tables, and impose constraints between tables.
The most important DDL statements in SQL are:

  • CREATE DATABASE – creates a new database
  • ALTER DATABASE – modifies a database
  • CREATE TABLE – creates a new table
  • ALTER TABLE – modifies a table
  • DROP TABLE – deletes a table
  • CREATE INDEX – creates an index (search key)
  • DROP INDEX – deletes an index

SELECT

If we want to select the content of the columns named “First_Name” and “Last_Name” from the table above, we would use the following SELECT statement:

SELECT First_Name,Last_Name FROM Persons

If we want to select all the columns from the “Persons” table, we would use the following SELECT statement:

SELECT * FROM Persons

SELECT DISTINCT Statement

In a table, some of the columns may contain duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.

The DISTINCT keyword can be used to return only distinct (different) values.

SELECT DISTINCT column_name(s)
 FROM table_name

For example, if we would like to see the distinct values from the column named “City” from the table above, we would use the following SELECT statement:

SELECT DISTINCT City FROM Persons

The result-set will look like this:

City
Corby
Ohio

The WHERE clause is used to extract only those records that fulfill a specified criterion.

For example, the

SELECT * FROM Persons
 WHERE City='Corby'

will return a result-set looking like this:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
2 Bob Builder The Main Road 23 Corby

Numeric values do not need to be included in quotes, but string values do. Use a single quote or a double quote.

Double quote require an extra setting in SQL to be turned on:

When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers

Operators Allowed in the WHERE Clause

With the WHERE clause, the following operators can be used:

Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns

If you would like to combine multiple conditions, you can use the logical operators AND and OR. You would use them separately or together, but remember, enclose conditions into open brackets.

SELECT * FROM Persons WHERE
 First_Name='Bob'
 AND (Last_Name='Builder' OR Last_Name='McGuire')

The result-set will look like this:

P_Id First_Name Last_Name Address City
2 Bob Builder The Main Road 23 Corby

The ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set by a specified column.

The ORDER BY keyword sort the records in ascending order (ASC) by default.

If you want to sort the records in a descending order, you can use the DESC keyword.

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

For example, if we want to select all the persons from the table above, sorted by their last name, we use the following SELECT statement:

SELECT * FROM Persons
ORDER BY First_Name

The result-set will look like this:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
4 Nilsen Tom Vingvn 23 Ohio
3 Peter Pan Neverland Ohio
2 Bob Builder The Main Road 23 Corby

INSERT STATEMENT

The INSERT INTO statement is used to insert new records in a table.

It is possible to write the INSERT INTO statement in two forms.

The first form doesn’t specify the column names where the data will be inserted, only their values:

INSERT INTO table_name

VALUES (value1, value2, value3,…)

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

We have the following “Persons” table:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
2 Bob Builder The Main Road 23 Corby
3 Peter Pan Neverland Ohio

Now we want to insert a new row in the “Persons” table.

We use the following SQL statement:

INSERT INTO Persons
VALUES (4,'John', 'Doe', 'Bat Street 2', 'Desborough')

The “Persons” table will now look like this:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
2 Bob Builder The Main Road 23 Corby
3 Peter Pan Neverland Ohio
4 John Doe Bat Street 2 Desborough

Insert Data Only in Specified Columns

It is also possible to only add data in specific columns.

The following SQL statement will add a new row, but only add data in the “P_Id”, “First_Name” and the “Last_Name” columns:

INSERT INTO Persons (P_Id, First_Name, Last_Name)
VALUES (5, 'Jacob', 'Powell')

The “Persons” table will now look like this:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
2 Bob Builder The Main Road 23 Corby
3 Peter Pan Neverland Ohio
4 John Doe Bat Street 2 Desborough
5 Jacob Powell

The UPDATE Statement

The UPDATE statement is used to update existing records in a table.

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Note: Notice the WHERE clause in the UPDATE syntax. The WHERE clause
specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!

Now we want to update the person “Tjessem, Jakob” in the “Persons” table.

We use the following SQL statement:

UPDATE Persons
SET Address='Bat Street 67', City='Corby'
WHERE First_Name='Jacob' AND Last_Name='Powell'

The “Persons” table will now look like this:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
2 Bob Builder The Main Road 23 Corby
3 Peter Pan Neverland Ohio
4 John Doe Bat Street 2 Desborough
5 Jacob Powell Bat Street 67 Corby

SQL UPDATE Warning

Be careful when updating records. If we had omitted the WHERE clause in the example above, like this:

UPDATE Persons
SET Address='The bad turn', City='Corby'

The “Persons” table would have looked like this:

P_Id First_Name Last_Name Address City
1 Jill McGuire The bad turn Corby
2 Bob Builder The bad turn Corby
3 Peter Pan The bad turn Corby
4 John Doe The bad turn Corby
5 Jacob Powell The bad turn Corby

The DELETE Statement

The DELETE statement is used to delete rows in a table.

SQL DELETE Syntax

DELETE FROM table_name

WHERE some_column=some_value

Note: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted.
If you omit the WHERE clause, all records will be deleted!


SQL DELETE Example

The “Persons” table:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
2 Bob Builder The Main Road 23 Corby
3 Peter Pan Neverland Ohio
4 John Doe Bat Street 2 Desborough
5 Jacob Powell Bat Street 67 Corby

Now we want to delete the person “Jacob Powell” in the “Persons” table.

We use the following SQL statement:

DELETE FROM Persons
WHERE First_Name='Jacob' AND Last_Name='Powell'

The “Persons” table will now look like this:

P_Id First_Name Last_Name Address City
1 Jill McGuire Oakley Hay 9 Corby
2 Bob Builder The Main Road 23 Corby
3 Peter Pan Neverland Ohio
4 John Doe Bat Street 2 Desborough

Delete All Rows

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

DELETE FROM table_name
or
DELETE * FROM table_name
or
TRUNCATE TABLE table_name

Note: Be very careful when deleting records. You cannot undo this statement!

Database Fundamentals in SQL Server 2008 p2

This lesson will provide you with an overview of the rich set of features and benefits that SQL Server can bring to your organization. You will gain an understanding of the components that are provided by SQL Server 2008. Finally, this lesson will summarize the typical roles and responsibilities of IT professional careers around SQL Server technologies. SQL Server is an example of a technology that has uses a client/server architecture.

Introducing SQL Server 2008 in the Enterprise

What Is a Client/Server Architecture?


The client/server architecture consists of client, server, and network components.

The client is the requester of services while the server is the provider of database services. You can design an application in client/server architecture and the users can access the designed application from client computers.

To help the users access the application, you need to install the client application on the client computer. You also need to install the server-side application on the server that has server software, such as Microsoft SQL Server 2000 or SQL Server 2008.

The user can access SQL Server 2008 by using Named Pipes or Multi-protocol Net-Libraries and share Windows NT. SQL Server 2008 grants access to the server based on Windows NT user or group security identifiers (SIDs).

In Microsoft Access there is only file sharing whereas in client/server architecture there is sharing of processes. When the client sends a request for a service or data to the server, the server responds to the request.

You can categorize client/server applications as multi-tier architecture based on how the business logic is distributed and whether the maximum processing takes place on the client or the server.

You can find several benefits in using client/server architecture. Firstly, you can ensure data integrity because data is stored in a central location.

In addition, you can define business and security rules on the server and enforce these rules equally among all users.

Besides this, you can optimize network traffic because an RDBMS performs effective querying and returns only the data that an application requires.

Therefore, the application workload is shared between the client and the server, thus providing a better performance.

Moreover, client/server architecture allows better scalability of the processes.

When scaling, you can do vertical and horizontal scaling up, that is, you can either increase the configurations of the server for better

 

What Is Microsoft SQL Server 2008?


Basically, SQL Server 2008 is an RDBMS. However, it is an enterprise-class data platform with integrated Business Intelligence (BI) tools that stores, manages, manipulates, and presents data. You can provide secure multiuser access to your data by using the client/server architecture of SQL Server 2008. Even if there are several can be, the solution is highly available and scalable.

SQL Server 2008 is designed to meet the exploding data needs of many organizations. SQL Server provides a solution that organizations can use to store and manage many types of data, including XML, e-mail, time/calendar, file, document, and geospatial data. It also provides a rich set of services to search, query, analyze, report, and integrate data.

You can use the components of SQL Server 2008 such as Microsoft SQL Server 2008 Analysis Services (SSAS), Microsoft SQL Server 2008 Reporting Services (SSRS), Microsoft SQL Server 2008 Integration Services (SSIS), Replication Services, and Notification Services to provide BI and enterprise data solutions. You can also take advantage of the new features available to SQL Server 2008 to add greater value to your organization’s data needs.

 

SQL Server Components


SQL Server 2008 also consists of a series of components that allows IT professionals to manage all aspects of database administration, development, and business intelligence development. The following list below is designed to provide an overview of the components purpose within SQL Server.

Relational Database Engine

Relational Database Engine provides a highly secure environment to store, retrieve, and modify data in relational, Extensible Markup Language (XML) and unstructured formats. Besides data management, the database engine enables you to set up security and manage the databases.

SQL Server Component Description
SQL Server Agent SQL Server Agent is a separate service that is used to automate repetitive tasks. Besides setting up job schedules, notifications and alerts can also be set up to alert a database administrator about problems or the successful completion of a job. Furthermore, you can set up alerts to monitor the general status of SQL Server and its databases.The SQL Server database engine must be running for the SQL Server agent to run. There are also tools to monitor the job history of the previously completed jobs.
Transact-SQL (T-SQL) Transact–Structured Query Language (T-SQL) is the primary programming language that is used against SQL Server. Database developers primarily use it query and modify data and objects within the database such as tables, views, and stored procedures.Database administrators will typically use the graphical user interface to manage the day-to-day operation of SQL Server. T-SQL provides useful code that not only allows you to retrieve information about objects that have been created by a database developer, but also to set up administrative tasks, such as backup programmatically.

Understanding T-SQL will benefit any SQL Server professional. This is because the T-SQL programming language is more flexible than the equivalent options that are presented in the GUI.

SQL Server Integration Services (SSIS) SSIS is the service that is responsible for the movement and integration of data from a single or multiple data sources. It can be used for simple data transfers from one system to another. It can also deal with complex data movement that can be used to populate entire database systems such as a data warehouse.SSIS is referred to as an Extract, Transform and Load (ETL) tool. The popularity of SSIS is due to its ability to transform or change data during the data movement, providing consistency to the data to meet business requirements.
SQL Server Analysis Services (SSAS) SSAS is the service that allows the creation of multidimensional structures that contains data aggregated together from other data sources. This multidimensional structure is referred to as a cube. Each intersection of axes within the cube is represented by the aggregation of data at that point. This ultimately delivers Online Analytical Processing (OLAP) from a central point, rather than having to establish this aggregated data from numerous data sources, hindering performance. Data warehouses are typically used as the source to an OLAP database.Furthermore, you can take advantage of the information already in the cube through SSAS data mining. Data mining allows SSAS to identify patterns in the data, which may not be so obvious, by using built-in data mining algorithms. Data mining can also use relational source to pick trend as well as using cubes as a source.
SQL Server Reporting Services (SSRS) SSRS is the reporting tool that can query a wide range of data sources, such as relational and OLAP, and present the information in a more digestible manner. It consists of a developer tool known as Report Designer to provide the professional report writer the ability to create compelling, visual reports. It also has an end-user tool known as the Report Builder that enables business users to create their own ad hoc reports.Visualization comes in the form of charts and gauges. It also involves presenting data in a standard table format. SSRS also provides the feature to automatically deliver reports through a wide range of formats.
Full Text Search Now integrated into the database engine in SQL Server 2008, Full text search enables fast and flexible indexing for keyword-based queries of free text data stored in a SQL Server database.
Service Broker Service Broker is a queuing mechanism that can be used for reliable, transactional, and message-based communication between software services. These services can be a part of applications within a single database or distributed applications.

The service broker service is a part of the database engine and is useful when informational messages from an application may not be immediately available to a backend database. This type of communication is known as asynchronous communication. The messages are stored in a queue until the receiving service becomes available. Introduced in SQL Server 2005, it was difficult to develop asynchronous applications prior to this. With Service broker, Microsoft provides the infrastructure r to manage asynchronous communication. At the same time, it also reduces the development time for such systems.

Replication Replication allows a copy of the data to be made available on another SQL Server so that the data is brought closer to the user. It also ensures that the data is synchronized.

Although replication can extract a copy of data from a source and load it into a destination, it cannot transform the data.

Replication can be controlled to replicate changes immediately or on a scheduled basis. It also has the ability to merge data together from multiple SQL Servers and reconcile conflicting data.

 

Database Fundamentals in SQL Server 2008


This course is aimed toward those students who are new to using Microsoft SQL Server.
This course is also useful to those students who are studying for exams:

•70-432: Microsoft® SQL Server® 2008, Database Administration

•70-433: Microsoft® SQL Server® 2008, Database Development

Lesson Introduction

A database is a repository of data that can be stored, manipulated, and retrieved. There are many database models. SQL Server specializes in the implementation of a relational database model.

The purpose of this lesson is to explore the fundamental components of a relational database system. Tables and relationships form the basis of SQL Server. The organization of these tables are known as normalization, which makes working with SQL Server efficient, regardless of whether you are writing queries or creating a database from the outset.

Finally, you will be introduced to additional objects that are implemented within a SQL Server database to support data.

Types of Databases

A relational database is a collection of tables that are related together by a common field through a relationship. The purpose of a relational database is to break down data into business entities such as customers, orders, and employees and reflect each entity as a table. This helps reduce data redundancy and improves performance.

Within a database, two models of database can be created.

Online Transactional Processing (OLTP) Online Analytical Processing (OLAP)
The tables are organized in a way that the database is more optimized for systems that perform data modifications such as inserts, updates and deletes.

Typically, deals with detailed information.

Users can still read data from these systems. However, read performance may be affected because the structure of these tables is designed to reduce the amount of redundant and repetitive data OLTP is commonly implemented in many relational database systems.

Data is up to date and is immediately available after the transaction that modified the data is complete.

Each transaction typically deals with small amounts of data.

In this model, data is stored is in multidimension views of aggregated and detailed data. These can be stored as tables in a SQL Server database, other storage models also exist.

Typically, deals with summary information.

The tables here are optimized for reading data. Although it is possible to modify data, the performance against OLAP may be impaired for write operations.

OLAP databases are managed by SQL Server Analysis services and is becoming popular with the need to analyze data becoming more of a business imperative.

OLAP represents historical data over time.

Each transaction typically deals with large amounts of data.

What Are Tables?

Tables are objects that are used to store data. A table consists of columns and rows.

Columns have a data type such as an integer or time data type associated to a specific column to control the type of data that can be entered.

Rows are used to hold specific information about a business entity such as a customer or an order.
Typically, each row consists of a unique identifier associated to a particular row such as a customerID or as in the graphic, the DepartmentID column to assign a unique attribute to a record.

A primary key constraint is typically associated with the ID column to ensure that the entity data is unique.

What Are Relationships?

Relationships are used to join tables together through common fields between two tables.

Primary keys can be used to enforce uniqueness of records within a single table. You can also use the primary key to enforce data integrity between two tables. This is known as referential integrity.
In a typical two table relationship, one of the tables will be joined to the other table by a primary key column. The table that has the primary key is known as the primary table, and all records in the primary key column are unique. The secondary table on which the column is joined from the primary table typically has repeating values. The column that holds the repeating values is referred to as a foreign key column. This creates a relationship known as one to many and is the most common relationship type used.

By default, the impact of this relationship on joined columns is:

 

  • A value cannot be placed in the secondary table unless it exists in the primary table.
  • A record cannot be removed from the primary table if it has related records existing in the secondary table.
  • A record cannot be updated from the primary table if it has related records existing in the secondary table.

This ensures that data integrity is retained and that accidental deletions and updates cannot occur. This default behavior can be modified. Additional relationships that can occur in a relational database management system include the following.

  • One-to-One. There is one record in the secondary table for each record in the primary table.
  • Many-to-Many. There are many records in the secondary table for many records in the primary table.

What Is Normalization?

Normalization is the process of organizing data and tables within the SQL Server database. This is implemented by creating tables and setting relationships between the table to remove repetitive and redundant data.
Redundant data occupies unnecessary space in the database. Moreover, a change in information in one area requires a change in all areas where the same data exists because data is repeated in several locations. This can be particularly problematic if the data changes frequently.
There are many degrees of normalization. For use in commercial databases, it is typical to only implement one of three levels of normalization. These typically set rules for how data should be broken down.

These are referred to as:

 

  • First Normal Form (1st NF)
  • Second Normal Form (2nd NF)
  • Third Normal Form (3rd NF)

Normalization is a useful guide in breaking down data into a relational format. However, there can be slight variations when it is implemented. Ultimately, remember the purpose of normalization.

  • To prevent waste of space
  • To make data consistent
  • To ease long-term maintenance
  • To ease data updates and modifications

Additional SQL Server Objects


Tables are the fundamental storage object of data within a SQL Server 2008 database. There are also additional objects that can be created in SQL Server, which are used to support, manage, and protect the data that is stored within the tables. The following is an overview of each object and the purpose that it serves within SQL Server.

SQL Server Object Description
Constraints Constraints are objects that are associated with single or multiple columns within a table. Constraints ensure that data that is inserted or updated meets the requirements that the constraint places on the data.

The type of constraint objects that can be associated to a table are:

Primary key

Unique constraints

Foreign key

Default constraint

Check constraint

XML schemas

Indexes Indexes are objects within SQL Server that provide pointers to the data. They act as a bridge between queries and the data.Indexes can speed up data retrieval from a database if the appropriate indexes are created.

Without indexes, SQL Server scans an entire table to find data that will be required in a query.

Moreover, like an index in a book, an index in a database requires disk space.

Stored Procedures Stored procedures are SQL Server objects that encapsulate T-SQL logic within a named object.Stored procedures can hold T-SQL statements for repetitive or complex T-SQL queries and tasks without the need to rewrite the T-SQL statement.

The main advantage of stored procedures is that they are precompiled.

This means that the first run of a stored procedure has to work out the best way to retrieve data for a query or a task before it is executed.

Subsequent execution of the same stored procedure will take advantage of the previous execution plan that was previously worked out.

This can substantially improve the performance of queries and modifications that are conducted through stored procedures.

Stored procedure objects can also be secured, so access to the data can be controlled.

Views Views are objects in SQL Server that are analogous to virtual tables. Views consist of rows and columns like a table, and they can be secured to control access to the data like stored procedures.Views can pull data from any number of tables into a single virtual table. This provides the following benefits:

Focuses the data for the user.

Simplifies the management of permissions.

Hides the complexity of the underlying tables.

Provides shared application logic for .NET applications.

In certain situations, views can be used to improve performance.

Functions Functions are objects within SQL Server that can be used to manipulate or change data.SQL Server provides a wide range of system-supplied functions that can be used against data in the database, such as:

CONVERT – To covert data from one data type to another

GETDATE() – To retrieve today’s date

However, SQL Server provides database professionals with the ability to create user-defined functions if the SQL Server system functions do not provide the required functionality.

Data Manipulation Language (DML) Triggers Like stored procedures, triggers are SQL Server objects that encapsulate T-SQL logic within a named object.However, triggers are event-driven. A trigger is an object that is associated to a table or a view and dependant on the type of trigger defined will only fire when one of the following T-SQL statements are issued:

Insert

Update

Delete