Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

 
  SQL Server Tips by Gama and Naughter

 

Nondeterministic UDF’s
 

Any UDF that returns values by calling an XP is considered nondeterministic because it is possible that the XP is nondeterministic. Although all the nondeterministic system functions are not allowed in a UDF, they can be called indirectly through a view.

 

Example: a UDF that returns a random number between one and ten.
 

Using rand() would cause an error when trying to create the UDF but calling an auxiliary view will work fine. This is the code for the view:

 

CREATE VIEW dbo.VIEWRandom

AS

SELECT ROUND(RAND() * 9 + 1, 0) AS RandomNumber

 

This is the UDF:

CREATE FUNCTION UDFRandom()

--UDF that returns a random number

RETURNS  int

AS 

BEGIN

RETURN (SELECT RandomNumber from VIEWRandom)

END

 

Inline Table-valued UDF's
 

This type of UDF works like a “parameterized view” because it consists of a single SELECT statement usually affected by the input parameters of the UDF. There are no more statements other than one SELECT or more if UNION is used.

 

Example: In database Pubs, create a UDF that will return the names of all employees hired between two dates. These two dates will be the input for the UDF:

 

CREATE FUNCTION UDFEmployeeHireDateRange(@FromDate datetime, @ToDate datetime)

--returns the employees hired within a certain time frame

RETURNS TABLE AS 

RETURN (

SELECT fname, lname, hire_date

FROM  dbo.employee

WHERE (hire_date >=@FromDate ) AND (hire_date <=@ToDate )

 

Calling the UDF:

 

SELECT * FROM dbo.UDFEmployeeHireDateRange('1-1-91', '4-15-91')

 

There is one problem with dates, their format depends on the collation and different users might have different settings. The above call would cause an error if the date format were different from the US standard. To make this call capable of running without caring about the collation, it is preferable to use the ISO8601 (yyyy-mm-dd) format:
 

SELECT * FROM dbo.UDFEmployeeHireDateRange('1991-1-1', '1991-4-15')


The above book excerpt is from:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm
 

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter