Database Reference
In-Depth Information
■
whenever you are working with parameters like
@OrderDate
, list the name of the parameter on the left
side of the assignment operator
=
and the argument
@TodaysDate
on the right side. This may seem backwards for
beginner programmers who are used to reading an expression from left to right,
(1
+
1
=
2)
, but in programming the
code is reversed:
(2
=
1
+
1)
.
Note
Like all things in programming, stored procedures have both good and bad qualities. For the most part,
their good qualities far outweigh their bad ones. Indeed, most programmers would be hard-pressed to find a
strong negative aspect to using stored procedures in your ETL processing. But if you look hard enough, you will
discover that stored procedures, when used in conjunction with SSIS packages, require special configurations:
not difficult, just special. We look at how stored procedures are configured in SSIS in Chapter 7.
Stored procedures cannot be used as an expression. In other words, you cannot integrate a stored procedure
call within a SQL select statement and have the stored procedure execute for each individual row. This is yet
another aspect of stored procedures that is not all bad per se, but notable. One way around this issue is the use of
user-defined functions (UDFs).
User-Defined Functions
Like views and stored procedures, UDFs consist of a named set of SQL statements. Unlike views or stored
procedures, they can be used as an expression. For example, the
GETDATE()
function will evaluate into the
current date much as the expression
5
+
6
will evaluate into
11
, such as in the SQL statement in Figure
6-15
.
Figure 6-15.
Using a function as an expression
The syntax for creating a UDF is quite similar to that of a stored procedure. First, list the name of the UDF,
followed by a list of any parameters that you want to use and then the return type of the function. The return type
of a stored procedure is always an implied integer, but UDFs, on the other hand, can return either a single value
or a table of values. Listing 6-28 shows an example of this.
