Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Define Your Own Functions in SQL Server 2000-3 : Page 3

Multi-Statement, Table-Valued Functions
The major difference in the way that you define a multi-statement, table-valued function from the previous example is that you must declare the table that you will be returning.

Begin your improved version of the GetCustByRank function with the following statement:

create function fn_GetCustByRankv2 ( @rank int ) returns @GetCustByRank table ( customerid nchar(5), totalsales money, rank int ) as begin

This function accepts a parameter called "rank" and returns a table variable called @GetCustByRank that has three columns: id, total sales, and rank.

Now you can get on with performing the necessary calculations. Because you can now use multiple statements, use another table variable to store the distinct number of total sales:

declare @tsales table ( totalsales money ) insert into @tsales select distinct totalsales as 'totalsales'

Then use the tsales table variable to perform your calculations and insert the results into the @GetCustByRank table variable:

insert into @GetCustByRank select s1.customerid,s1.totalsales,(select count
(distinct s2.totalsales) from @tsales s2 where s1.totalsales <= s2.totalsales) from vw_totalsales s1 where (select count (distinct s2.totalsales) from @tsales s2 where s1.totalsales <= s2.totalsales) <= @rank return end

For the sake of clarity, see Listing 3 for the function in its entirety.

Referring to Functions
Now that you've created three functions, test them out. Because fn_getnextdate() is a scalar function, you can simply type the following statement to find the Thursday after March 2, 2002:

select dbo.fn_getnextdate('03/02/02',5)

Notice that the function name is qualified with the owner. This is unlike system-supplied functions that you can simply refer to by name—as in SELECT GETDATE(). If you aren't in the database in which you created the function, you will also need to qualify the function name with the database name. In my example, because I've created all the functions in the Northwind database, if I was in the pubs database, I would need to write the following SQL:

select Northwind.dbo.fn_getnextdate('03/02/02',5)

The fn_GetCustByRank and fn_GetCustByRankv2 functions both return tables, so they must be treated like tables. The proper way to execute them in a select statement is as follows:

select [whatever columns I'm interested in] from dbo.fn_GetCustByRank

For example:

select * from dbo.fn_GetCustByRank

If you use the wrong syntax, you will get a rather misleading error message. For example if I type select dbo.fn_GetCustByRank (6), leaving out the columns that you want, you get the following error message: "Invalid object name 'dbo.fn_GetCustByRank'." In reality, the function exists; you just didn't invoke it properly.

Joe Lax has spent the last 10 years working in various database environments and has been a practicing DBA on all versions of SQL Server from version 4.2 onward. Joe is also a MCSE and an MCT. Recently, he has started to learn Oracle, which affords him no end of fun.
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