Tag Archives: Transact SQL

Introduction to Stored Procedures

A stored procedure is a batch of Transact-SQL (T-SQL) code that is saved internally in SQL Server as a database object. You can use stored procedures to mask complex SQL tasks from clients, improve the performance of your application, enforce data and business rules, increase multi-user concurrency, and reduce locking and blocking conflicts. It is a module of T-SQL code that can perform just about any task in SQL Server, accessing data as well as
resources outside of SQL Server. A stored procedure is the workhorse of SQL coding, and in this chapter you’ll learn about how to use this important database feature.

Stored Procedure Features

Unlike scripts, which are saved outside your database as text files, stored procedures are saved as objects inside your database. You can execute a stored procedure from a client application, a batch or script, or from other stored procedures. Stored procedures are parsed and syntax-checked when you create them and are compiled on first execution. SQL Server caches the execution plan and shares it among all users of the database. Subsequent calls to the stored procedure can reuse the cached plan, even if different users call the stored procedure. This compilation, caching, and sharing of execution plans gives stored procedures a significant performance advantage over running ad hoc T-SQL statements. You can also lock down the security of your database by revoking permissions on database objects and only granting access to data and objects through a stored procedure.

Unlike views, which are also saved as database objects, stored procedures support the full capabilities of Transact-SQL. A single stored procedure can contain up to 250 megabytes of text, a limit you won’t encounter anytime soon (or, if you do, you should seriously consider refactoring your code!) Stored procedures can accept input parameters and can return more than one result set, and they can also return data in the form of output parameters and a return value. Stored procedures are as close as you can come to functions or methods that you’ll find in full-featured, object-oriented programming languages.

TIP: SQL Server uses stored procedures in most of its internal operations. System stored procedures have a schema name of sys and an sp_ prefix. You can read the Transact-SQL definitions of these stored procedures by running the sp_helptext system stored procedure and supplying the stored procedure name(for example, sp_helptext ‘sys.sp_tables’). You’ll find that most contain explanatory comments as well. Exploring system stored procedures is a great way to learn advanced T-SQL programming techniques once you’ve mastered
the basics.

The only tasks that you cannot complete with a stored procedure are the creation of triggers, defaults, rules, other stored procedures, and views. A stored procedure can do everything from executing a basic SELECT statement to enforcing complex business logic and explicit transactions, as well as modifying data and other database and system objects.

 

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Stored Procedures course written by expert Don Kiely.Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Overview of Transact-SQL (T-SQL)

Transact-SQL is the SQL Server implementation of SQL, a standard codified
by the American National Standards Institute (ANSI) and also adopted by the
International Organization for Standardization (ISO). No single vendor has
fully implemented every part of the ANSI/ISO standard, and each vendor has
added its own proprietary extensions to the language, so you’ll find plenty of
things in Transact-SQL (T-SQL) that you won’t find in other database
products. The standard is updated roughly every three or four years, and so is a
moving target that no SQL product ever fully implements.

The SQL language came about as a result of the work that Dr. E. F. Codd did
in the 1960s on his Relational Database Model. The first version of the
language was known as SEQUEL. It was then completely rewritten in the
seventies, and eventually became known as SQL for Structured Query
Language, because it turned out that the acronym SEQUEL had already been
trademarked. The original “sequel” pronunciation has stuck to this day–SQL
Server is still widely referred to as sequel server, although some purists insist
that the language name should be pronounced ess-que-ell. However you
pronounce it, the SQL standard has been relatively well received and is the
most widely supported standard today.

Transact-SQL (T-SQL)

Transact-SQL is Microsoft’s implementation of the SQL language in SQL
Server. The language implements a significant subset of the features of
standard SQL, as well as some very useful extensions to the ANSI standard
that add procedural capabilities, which makes Transact-SQL more like a
programming language. There are control-of-flow features, such as IF…ELSE
syntax and WHILE loops, as well as support for variables, parameters, and
user-defined functions. Like other programming languages, Transact-SQL
supports built-in functions for manipulating strings, numbers, and date/time
information, and for returning system information.

Although Transact-SQL has programming language features, you’d never want
to use Transact-SQL to replace a general purpose programming language. It

has no user interface and its programming constructs are very limited. The
main advantage to programming in Transact-SQL is that your routines execute
on the server and are highly efficient for set-based data operations. Transact-
SQL provides the building blocks for all your views, stored procedures, userdefined
functions, and triggers. When you perform as much processing as
possible in Transact-SQL, performance improves because less data has to
traverse the network for processing on the client.

T-SQL has two broad sets of statement types: data definition language (DDL)
and data manipulation language (DML) statements. DDL statements let you
create and update database and server objects, while DML statements let you
work with the data itself to create, retrieve, update, and delete rows of data.
These four operations are so common in relational databases that they are often
collectively referred to as CRUD operations.

Microsoft adds a number of new T-SQL features in every new version of SQL
Server, some of which are proprietary to that product, while others are
implementations of the features in the SQL standard. This chapter focuses on
the fundamentals of the T-SQL language.

NOTE

SQL Server hosts the .NET Common Language Runtime (CLR),
which allows you to write code in any supported .NET language,
such as C# or Visual Basic. You can use Visual Studio to write
programs that are then compiled into assemblies and loaded into
SQL Server. CLR procedures are designed for code that is
processor-intensive, such as complex mathematical or string
manipulation. The CLR is not designed to replace Transact-SQL,
which is always the best choice for data access and for set-based
operations. Any time a CLR procedure performs data access, it
uses Transact-SQL “under the covers.”

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

Updating Large Value Types with UPDATE .WRITE

SQL Server provides an UPDATE .WRITE Transact-SQL statement to perform partial updates on columns that are defined as varchar(max), nvarchar(max), or varbinary(max). Here is the abbreviated syntax:

The following example uses the Production.Document table in the AdventureWorks database to demonstrate substitution of the word “critical” for the word “important” in one row of data with a DocumentNode value of 0x5B40.

First, select the row to view the existing data:

The first sentence in the results should look like the Figure below (you’ll need to expand the width of the column to view the entire text of the column).

Updating Large Value Types with UPDATE .WRITE

To change the word “important” to the word “critical” you need to specify the offset (the number of characters from the start of the string to the value you want to replace) and the length of the string to replace.

If you execute the SELECT statement again to view the current value, you’ll see the results as shown in the Figure below.

Updating Large Value Types with UPDATE .WRITE

Execute the following statement to restore the original value:

You can also use the CHARINDEX and PATINDEX functions to calculate the offset location of the substring you want to change. The LEN function returns the number of characters in a string, so the following statement performs the same replacement of ‘critical’ for ‘important’. (In the sample code file, these statements are wrapped in a transaction that is rolled back, so you don’t have to worry about executing another UPDATE statement to undo the change.)

WARNING! In past versions of SQL Server, WRITETEXT was used to replace sections of long text values. SQL Server 2012 still supports this statement, but it has been deprecated and will be removed in a future version of SQL Server. Therefore, you should always use the new .WRITE clause of an UPDATE statement rather than using WRITETEXT.

 

Thumbnail for 566This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.