hose of you who program in Visual Basic, C, or other languages are probably used to writing your own functions. As a SQL programmer, you know how frustrating it's been not to have this ability in previous versions of SQL Server.
In this 10-Minute Solution, I demonstrate how to create and utilize user-defined functions in SQL Server 2000.
The Limitations of Stored Procedures
Let's first take a look at why you might want to create functions in SQL Server in the first place. After all, you have always been able to create stored procedures. Isn't that enough?
Well, stored procedures have a very basic limitation; namely, you can't use one as part of a
SELECT statement other than to set a variable. For example, if you had business logic that performed a calculation on an invoice, you could not have written the following code (until SQL Server 2000 came out):
select client, my_calc(invoice)
group by client
Similarly, you couldn't join to the results of a stored procedure. Let's say you had a procedure that identified your best customers and you wanted to pull the invoices for those customers to research their buying habits. The following code would have been the most convenient method, but until SQL Server 2000, it wouldn't have been possible:
select client, invoice, amount
inner join my_best_customers() as w
on w.client = invoices.client
As I demonstrate, SQL Server 2000 allows you to do all of the above, and more.
How can I write a user-defined function in SQL Server 2000?
You can create a user-defined function in three ways:
- Create a scalar function to return a single value.
- Use an inline table function to return a table of results. However, you can't have more than one SQL statement in the function.
- If your SQL is more complicated, you can use a multi-statement, table-valued function by defining the table that will be returned.