Filtering Data with User-Defined Functions


Filtering Data with User -Defined Functions

  • Filter data using user-defined functions.

User-defined functions can be very handy to have around for use in a WHERE clause or ORDER BY clause in a select statement to limit the number of returned rows. Extending the sales rep example, what would happen if the sales manager wanted to find out how his salespeople ranked by the amount of commission that they earn? A query could be written like this:

 SELECT SalesRepName, master.dbo.GetCommission(SalesrepID) FROM SalesRep ORDER BY 2 

Another example would be to write the same thing, but only for sales reps with more than $50,000 in sales, which would be done like this:

 SELECT SalesRepName, master.dbo.GetCommission(SalesrepID) FROM SalesRep WHERE dbo.GetCommission(SalesrepID) > 50000 ORDER BY 2 

That's how you can use user-defined functions to filter data ”the same way you use system functions to filter data, and the same way you use a WHERE clause to filter data. Just plug in the user-defined function and you're ready to go.

You've already seen how table-valued, user-defined functions can be used to filter data. Look again at the preceding TableDistance function, which returns a filtered data set. An extension to that would be to use it in the FROM clause to restrict how data is returned. If you have a set of points you are interested in keeping track of, like this:

 CREATE TABLE PointsOfInterest( PointID int IDENTITY(1,1), LocationID int, PointDescription varchar(100) ) insert into PointsOfInterest values (4, 'Times Square') 

Then you can populate the PointsOfInterest table, and each location can have several points of interest associated with it. Then you can use the TableDistance function to filter the data, like this:

 SELECT POI.PointDescription FROM PointsOfInterest POI INNER JOIN dbo.TableDistance(4,1,1) TD on TD.ID = POI.LocationID 

This finds all the points of interest that are within one unit of the coordinate (4,1), which is Times Square.



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net