Limitations

for RuBoard

There are a number of limitations and restrictions that apply exclusively to UDFs:

• Except for inline functions, the outer BEGIN END pair is required with UDFs. This differs from procedures and triggers. Curiously, the outer BEGIN END enclosure is not only not required with inline functions, it's not allowed; you'll get a syntax error if you try to include it.

• Calls to scalar UDFs must be owner qualified. Note the dbo . prefix on the call to the Proper() function used earlier. There's an undocumented way around this requirement that we'll discuss momentarily.

• The last statement in a UDF must be RETURN.

• There are a number of restrictions on the T-SQL you can use inside a function. Basically, you aren't allowed to do anything that might have a side effect. For example, you can't create permanent objects of any type, you can't reference temporary tables in any way (even preexisting ones), and you can't call stored procedures. You can create table variables, but they have limitations (for example, you can't use user -defined data types in table variables and you can't use INSERT EXEC or SELECT INTO with them). You can call extended procedures, but only those that are named with the prefix xp. Some extended procedures (e.g., sp_executesql and sp_xml_preparedocument) are extended procedures from the perspective of Enterprise Manage, but they're prefixed with sp instead of xp. You can't call these from a UDF. My xp_exec extended procedure can work around this to some extent because it allows you to run a T-SQL command batch of your choosing via an extended procedure interface. See the section entitled, "Parameterized UDFs" for more info .

• You cannot call RAISERROR() from a UDF to report errors or to set @@ERROR.

• Many environmental settings cannot be changed from within a UDF, even those that you can normally use in a stored procedure and that only last for the duration of the procedure. SET NOCOUNT ON, for example, is not allowed.

• You cannot call a UDF using a four-part name . For example, you can't do this.

  Listing 10-4 A workaround for calling remote functions.

   SELECT kufnahte...calc_interest(100000,7.6,30) 

  You can work around this limitation to some extent using rowset functions such as OPENQUERY(), like this:

   SELECT *FROM OPENQUERY(kufnahte, 'SELECT dbo.calc_interest(100000,7.6,30)') 

  However, OPENQUERY and its siblings do not allow a variable to be supplied as the query textit must be a literal stringtherefore, you can't pass parameters to the queries you executea horrible limitation. The only suitable workaround I've found is to use sp_executesql with an output parameter, like this (Listing 10-4):

   DECLARE @interest intEXEC kufnahte...sp_executesql N'SELECT@int=dbo.calc_interest(@prin,@rate,@years)',N'@prin int, @rate int, @years int, @int int OUT', 100000, 7.6, 30,@interest OUTSELECT @interest-----------210000 

  This is quite a bit of work just to call a function. You'd probably be better off either using a stored procedure instead or copying the function to the local server.

• Optional UDF parameters are not truly optionalyou can't actually omit them. If you want to leave out a parameter to a UDF, you must supply the DEFAULT keyword, even if it's the only parameter to the function. This can be a bit of a pain when a function has lots of optional arguments. Listing 10-5 illustrates:

  Listing 10-5 Optional UDF parameters are not truly optional.

   CREATE FUNCTION dbo.Sprintf(@FmtStr varchar(255) ,@Parm0 varchar(255) ,@Parm1 varchar(255)='' ,@Parm2 varchar(255)='' ,@Parm3 varchar(255)='' ,@Parm4 varchar(255)='' ,@Parm5 varchar(255)='' ,@Parm6 varchar(255)='' ,@Parm7 varchar(255)='' ,@Parm8 varchar(255)='' ,@Parm9 varchar(255)='' ) RETURNS VARCHAR(255) AS BEGIN DECLARE @Result varchar(255) EXEC master..xp_sprintf @Result OUT, @FmtStr, @Parm0, @Parm1, @Parm2, @Parm3, @Parm4, @Parm5, @Parm6, @Parm7, @Parm8, @Parm9 RETURN(@Result) END GO DECLARE @Artist varchar(30), @Song varchar(30), @Band varchar(30) SELECT @Artist='Paul Rodgers', @Song='Fire and Water', @Band='Free' SELECT dbo.Sprintf('%s sang the song "%s" for the band %s',@Artist, @Song, @Band, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) 

  (Results)

   -------------------------------------------------------------------- Paul Rodgers sang the song "Fire and Water" for the band Free 

  See the difficulty? Because Sprintf() may take as many as nine optional string parameters, we have to supply DEFAULT repeatedlywe can't simply omit the extra parameters. In C/C++ terms, we can't create a " varargs " routine even though xp_sprintf, the internal routine that's doing the real work here, allows a variable number of parameters to be specified to it.

  NOTE

  You may have noticed that the maximum length of Sprintf()'s parameters and return value is only 255 bytes. This is because the extended procedure it calls to format the string, SQL Server's xp_sprintf, has the same limitation. Xp_sprintf, like many older extended procedures, makes use of the deprecated srv_paramdata() and srv_paramlen() ODS functions to retrieve the contents and length of the parameters passed into it. Unlike the newer srv_paraminfo() function, these routines do not support the newer (post-SQL Server 6.5) data types or their increased capacities . I've limited Sprintf() to 255 bytes because strings longer than that are clipped by xp_sprintf when returned.

  
  

• You may not call nondeterministic system functions from UDFs. A deterministic function is one that returns the same results each time it's called with the same parameters. A nondeterministic function is one that may notit may return different results from execution to execution even when called with the same parameters. You aren't allowed to call nondeterministic system functions from UDF's, nor are you allowed to use nondeterministic computed columns (e.g., one based on a nondeterministic system or user function) as index keys. Examples of nondeterministic system functions include GETDATE(), @@CONNECTIONS, NEWID(), and RAND(). See the Books Online for the complete list.

  Table 10-1. OBJECTPROPERTY() Options for UDFs

  Option	Use	sysobjects type value
  IsInlineFunction	Returns 1 for inline table-valued functions	IF
  IsTableFunction	Returns 1 for multistatement table-valued functions	TF
  IsScalarFunction	Returns 1 for scalar UDFs	FN
  IsDeterministic	Returns 1 for deterministic UDFs
  IsSchemaBound	Returns 1 for schema-bound UDFs

• You cannot create a PRIMARY KEY constraint on a computed column based on a UDF that may return NULL. A numeric UDF, for example, may return a NULL result because of an overflow or divide-by-zero error. The workaround is to wrap the computed column's expression in ISNULL() so that it's impossible for the expression to return NULL.

for RuBoard