Passing and Returning Parameters To and From User-Defined Functions


Passing and Returning Parameters To and From User -Defined Functions

  • Pass and return parameters to and from user-defined functions.

Parameters for user-defined functions are passed positionally in a comma-delimited list. There is nothing analogous to named-variable passing for user-defined functions; the arguments must be passed positionally. So, this works:

 print pubs.dbo.distance(1, 1, 2, 2) 

And this does not:

 print pubs.dbo.distance(@x1=1, @y1=1, @x2=2, @y2=2) --BAD CODE doesn't work 

The preceding code shows you how to print out the results, but what if you want to put the results into a variable? That would go something like this:

 declare @Distance float set @Float = pubs.dbo.distance(1,1,2,2) 

You can handle returned tables with an insert statement into a permanent table, a temporary table, or, in the following example, a variable of type table :

 declare @Distance table ( ID int, Dist float ) insert into @Distance select * from master.dbo.TableDistance(4, 3, 1) 

Calling user-defined functions is much less cumbersome and perhaps less confusing than calling stored procedures because there are no output parameters to deal with, no named parameters are possible, and there is only one output value.



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