Databases Reference
In-Depth Information
This time, we better step through the function so you can see what's going
on inside it. We declare an input parameter named @NUM and set its default
value to NULL. In the RETURNS clause we declare a table variable named @cust-
copy, along with the table definition. If @NUM has a value of NULL, then we
insert all rows from CUSTOMERS into the table variable. Otherwise, we filter
FOR EXAMPLE
Replacing Views?
Remember the “For Example” box in the last section. You want to be able to
retrieve data about employees, but filter the data returned by department and
job requirements. The solution was to create one view for the Human Resources
manager, another for payroll, another for the employee's manager, and so on.
You might think that another solution would be to create a multistatement
table-valued function to retrieve the data. When calling the function, you pass
the appropriate user type, such as HR or manager, to filter the result.
The problem is that this isn't really a workable solution in this situa-
tion. Why not? There are two major flaws. One is that a UDF lets you fil-
ter the result by row, but the same columns are always returned. The only
way around this would be to have the function return every column you
might possibly need, but return NULL values for inappropriate or unneces-
sary data. The problem has to do with security. You would need to embed
some type of security check inside the procedure, otherwise what is there
to prevent the employee's manager from running the function and passing
the parameter to return data for the HR manager?
There are, however, any number of possible situations for which a UDF
would be well-suited. Say, for example, that you need to run sales reports
by customer and salesperson each month so you can calculate commission
payments. As long as the salesperson number is associated with the sales
records, you could create a UDF to simplify this activity. Create a UDF that
lets you pass the salesperson number and then retrieves data for just that
salesperson. You could even take it a step further with a scalar function. You
could have the function use the salesperson number to retrieve the com-
mission percentage for you, calculate the percentage, and return the com-
mission amount as a scalar value.
The same general rule of thumb applies for determining access through
functions as it does for views. If you don't give people the information they
need to do their jobs, they will let you know. If you give them more than
they need, you may not find out until after the damage has been done.
