Browse DevX
Sign up for e-mail newsletters from DevX


Define Your Own Functions in SQL Server 2000

The article describes how to create and utilize user-defined functions in SQL Server 2000.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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) from invoices 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 from invoices 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:

  1. Create a scalar function to return a single value.
  2. Use an inline table function to return a table of results. However, you can't have more than one SQL statement in the function.
  3. If your SQL is more complicated, you can use a multi-statement, table-valued function by defining the table that will be returned.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date