Database Reference
In-Depth Information
In this example, we irst declare the variable @Count, which is used as the counter for the
WHILE loop. he initial value of @Count is set to 1. he WHILE loop is used to check all four
weekdays' classes. he BEGIN. . .END keywords are used to enclose a block of SQL Statements
for the WHILE loop. Inside the WHILE loop, if the value in the counter is equal to 1, which is
Monday, all the classes will be moved to Tuesday by adding 1 to DayID. If the value of the counter is
not equal to 1 and there are duplicated classes on the same day (meaning COUNT(CourseID) > 1),
the DayID value of the class with the largest class id will be increased by 1. he innermost subquery
groups the classes by CourseID and DayID. In the HAVING clause, the built-in function COUNT
is used to count the number of elements in each group speciied by the GROUP BY clause. If the
count of a course id is larger than 1, the course id will be returned by the subquery. In the outer sub-
query, the built-in function MAX is used to select the largest class id corresponding to the returned
course id. Based on the returned class id from the outer subquery, the SET clause increases the value
of DayID. For the IF. . .ELSE structure, if the code block contains more than a one-line statement,
BEGIN. . .END is needed to enclose all the statements. he last SET clause in the WHILE loop is
used to increase the counter so that the next loop will process the next day's classes.
As a procedural programming language, Transact-SQL allows users to create functions and
procedures. In the next section, you will learn how to create and manage user-deined procedures
and functions.
7.3 Procedures and Functions
To execute a set of SQL statements together automatically, you can place these SQL statements in
a function or procedure . Procedures are often used to perform activities while a function is used
to return a value. A procedure can have multiple input and output parameters. To run a procedure,
you need to explicitly use the keyword EXEC, and you cannot directly run a procedure in a query.
In contrast, Transact-SQL functions can be used within a query. A function can take multiple
input parameters but will only return a single output. he function output can be returned to a
variable or used by clauses such as FROM and WHERE. Let us start with functions.
7. 3 .1 F u n c t i o n s
here are two types of functions: built-in functions and user-deined functions.
Built-in functions are prebuilt in Transact-SQL. hese functions cannot be modiied by the
user.
User-deined functions are created by database users with the CREATE FUNCTION com-
mand. A user-deined function can only return a single value or a table and does not have
an output parameter.
he built-in functions were discussed in Chapter 6. In this chapter, our main focus is on user-
deined functions.
When a user-deined function returns a single value, it is classiied as a scalar function . If a
user-deined function returns a table, it is called a table function or table-valued function . A user-
deined function can be created by the CREATE FUNCTION statement with the following format:
CREATE FUNCTION [ owner_name .] function_name
([{@ parameter_name [AS] scalar_parameter_data_type [ = default ]}[ , ... n ]] )
Search WWH ::




Custom Search