Sql server tutorial 14 Creating SQL-Invoked Routines - XADAT.COM
post-template-default,single,single-post,postid-15812,single-format-standard,qode-quick-links-1.0,ajax_fade,page_not_loaded,,qode-theme-ver-11.1,qode-theme-bridge,wpb-js-composer js-comp-ver-5.1.1,vc_responsive

Sql server tutorial 14 Creating SQL-Invoked Routines No ratings yet.

Sql server tutorial 14 Creating SQL-Invoked Routines

Key Skills & Concepts

  • Understand SQL-Invoked Routines
  • Create SQL-Invoked Procedures
  • Add Input Parameters to Your Procedures
  • Add Local Variables to Your Procedures
  • Work with Control Statements
  • Add Output Parameters to Your Procedures
  • Create SQL-Invoked Functions

Prior to the release of SQL:1999, the American National Standards Institute (ANSI) and
the International Organization for Standardization (ISO) published an interim standard
in 1996 that added procedures and functions, along with related language, to the existing
SQL standard as Part 4. This new publication, also referred to as SQL/PSM, or PSM-96
(PSM standing for persistent stored module), represented the first step toward including
procedural capabilities within SQL itself. Part 4 (SQL/PSM) was revised and incorporated
into the SQL:1999 standard, revised again for the SQL:2003 standard, and corrections and
revisions have taken place in subsequent versions of the SQL standard. These procedural
capabilities define, among other components, the creation of SQL-invoked routines—
specifically, SQL-invoked procedures and SQL-invoked functions. In this chapter, we’ll
take a close look at both procedures and functions, including how to create them and how
to invoke them once they’re created. We’ll also take a look at a number of examples that
demonstrate the various types of procedures and functions and the components that make
up each.

Understand SQL-Invoked Routines

I first introduced you to the concept of SQL-invoked routines in Chapter 2, where I
describe the schema objects that can exist within an SQL environment. As you might
recall, an SQL-invoked routine is a function or procedure that can be invoked from SQL.
Both functions and procedures are stored sets of predefined SQL statements that perform
some sort of action on the data in your database. For example, you can define a SELECT
statement and store it as an SQL-invoked procedure. Once you have created that
procedure, you can invoke it simply by calling its name and, if appropriate, supplying the
necessary parameters.
Unlike views, all SQL-invoked routines support the use of parameters, which are
values passed to and from a routine when you invoke that routine. A function can receive
input parameters and return a value based on the expression included in the function
definition. A procedure can pass input and output parameters. Regardless of whether it’s a
procedure or function, an SQL-invoked routine can be a schema object or can be
embedded in an SQL server module, which is also a schema object. (A module is an object
that contains SQL statements or routines.)

The SQL standard also supports a third type of SQL-invoked routine—the SQL-invoked method. A method,
which is used in user-defined types, is a type of function that performs predefined tasks. SQL supports two types
of user-defined types: structured types and distinct types. Methods are used in structured types. The subject of
structured user-defined types is beyond the scope of the book, so I won’t be covering methods in this chapter.

Most SQL implementations support some form of the SQL-invoked routine in their
products. Within various SQL implementations, SQL-invoked procedures are often
referred to as stored procedures, and SQL-invoked functions are often referred to as userdefined
functions. Regardless of the names used, the fundamental concepts are the same,
and the basic functionality supported is similar from product to product. However, while
concepts and functionality are similar, the implementation of SQL-invoked routines can
vary widely, and the specifics of how SQL-invoked routines are created and called differ
not only between the SQL standard and the individual product, but also between the
products themselves. The main reason for this is that many products had already
implemented PSM technology prior to the initial publication of the SQL/PSM standard in
1996. As a result, proprietary functionality has persisted among the different
implementations, with few SQL products conforming to the actual SQL/PSM standard or,
consequently, the PSM-related portion of the SQL standard.

Despite the product differences, it is still worthwhile to have a look at the basic
concepts behind SQL-invoked routines as they are defined in the SQL standard. The
standard provides insight into the underlying structure used by the various SQL
implementations and can give you a cohesive overview of the basic concepts shared by all
products that implement SQL-invoked procedures and functions. However, as with other
SQL-related technology, you should refer to the product documentation for your specific
SQL implementation. In few cases will you be able to use pure (standard) SQL to create
an implementation-specific SQL-invoked routine.

SQL-Invoked Procedures and Functions
As I mentioned earlier, an SQL-invoked routine can be either an SQL-invoked procedure
or an SQL-invoked function (or, in the case of user data types, an SQL-invoked method).
SQL-invoked procedures and functions are similar in many ways, although there are some
basic differences. Table 14-1 provides an overview of the main differences and

0 >1>2>3>4>5>6>7>8>9>10>11>12>13>14>15>16>17>18>19>20>

No Comments

Sorry, the comment form is closed at this time.