Skip to Main Content
Programming SQL Server 2005
book

Programming SQL Server 2005

by Bill Hamilton
February 2006
Intermediate to advanced content levelIntermediate to advanced
586 pages
17h 35m
English
O'Reilly Media, Inc.
Content preview from Programming SQL Server 2005

Chapter 5. Programming SQL Server CLR Routines

This chapter demonstrates how to create each type of SQL Server 2005 CLR routine: user-defined functions (scalar-valued functions and table-valued functions), stored procedures, user-defined aggregate (UDA) functions, user-defined types (UDTs), and both DML and DDL triggers. All examples in this section use Visual Studio 2005 to create and compile the CLR routines. If you don’t have Visual Studio 2005, you can use the C# command-line compiler (csc.exe) discussed in Chapter 4.

Scalar-Valued Functions

A scalar-valued function (SVF) is a user-defined function (UDF) that returns a single value. Scalar-valued functions can take arguments and return values of any scalar data type supported by SQL Server except rowversion, text, ntext, image, timestamp, table, or cursor.

An SVF is implemented as a method of a class in a .NET Framework assembly. The return value of the method must be compatible with the SQL Server data type that the method returns. Table 4-16 lists SQL Server data types and their equivalent CLR data types.

You identify a .NET SVF or TVF by annotating the method where you implement the function with the SqlFunction attribute. In addition to indicating that the method should be registered as a function, the SqlFunction attribute can be used to define characteristics of the function. The SqlFunction attribute has the following syntax:

    SqlFunction [ ( function-attribute [,...] ) ]

    function-attribute::= IsDeterministic = {true | false} ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Programming Microsoft® SQL Server® 2012

Programming Microsoft® SQL Server® 2012

Leonard Lobel and Andrew Brust

Publisher Resources

ISBN: 0596004796Supplemental ContentErrata Page