Professional SQL Server™ 2005 Programming

14.5. Creating Scalar User-Defined Functions from Assemblies

Scalar functions are not much different from stored procedures. Indeed, for the most part, they have the very same differences that the T-SQL versions. Much as with stored procedures, we utilize the same core CREATE syntax that we used in the T-SQL user-defined functions (UDFs) we created back in Chapter 11.

CREATE FUNCTION [<schema name>.]<function name>
    ( [ <@parameter name> [AS] [<schema name>.]<scalar data type> [ = <default value>]
      [ ,...n ] ] )
RETURNS {<scalar type>|TABLE [(<Table Definition>)]}
    [ WITH [ENCRYPTION]|[SCHEMABINDING]|
        [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ] | [EXECUTE AS {
        CALLER|SELF|OWNER|<'user name'>} ]
]
[AS]{ EXTERNAL NAME <external method> |
BEGIN
    [<function statements>]
    {RETURN <type as defined in RETURNS clause>|RETURN (<SELECT statement>)}
END }[;]

There are one or two new things once you get inside of the .NET code. Of particular note is that there are some properties that you can set for your function. Among those, probably the most significant is that you must indicate if the function is deterministic (the default is nondeterministic). We'll see an example of this in use shortly.

For the example this time, start a new SQL Server project in Visual Studio, but instead of adding a stored procedure as we did in our original assembly example, add a user-defined function.

SQL Server starts you out with a simple template:

using System; using System.Data; using System.Data.SqlClient; ...