PROCEDURES AND FUNCTIONS IN .NET LANGUAGES Table-Valued Functions

PROCEDURES AND FUNCTIONS IN .NET LANGUAGES Table-Valued Functions A user-defined function implemented with a CLR language can return a table, just as any user-defined function in T-SQL can. These are called table-valued user-defined functions, or TVFs. A TVF must return an ISql Reader or ISqlRecord interface. These interfaces are implemented by the SqlDataReader, discussed in Chapter 4, which is used to return the results of a tabular query. Complete coverage of the implementation of ISqlReader and ISql Recordare beyond the scope of this book, but we will cover the key points. A TVF acts like a cursor on a set of records. Each time you call Read, you advance the cursor to the next record. While the cursor is on a record, it allows you to access the fields in that record. The implementation of ISqlReader implements the cursor that moves over a set of records, and ISqlRecordprovides access to the fields in each record. For ISqlReader, the principal elements you must implement are the Read() method and the HasRows and FieldCount properties. Read() advances the cursor and returns true if the resulting cursor has not gone beyond the last row. HasRowsreturns a booleanthat indicates that there is at least one row. And FieldCountreturns an intthat indicates the number of fields in a row. To illustrate how to implement a table-valued function, we will implement one that produces a sequence of numbers called a Fibonacci sequence. The sequence starts with two numbers, and each number after the second one is the sum of the previous two. Here is an example. 1 2 3 5 8 13 21 1+2 2+3 5+3 8+5 13+8 The TVF we will create is a function called Fibonacci that will return two columns, one called next and one called previous. It will take as input the first two numbers we want in our sequence and the length of the sequence. The following SQL script runs our function and produces a table with six rows. select next, previous from dbo.Fibonacci(1,2,6) next previous – 2 1 3 2 5 3

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

Bookmark the permalink.

Comments are closed.