Types of User Defined Functions

Types of User Defined Functions
There are three different types of User Defined Functions. Each type refers to the data being returned by the function. Scalar functions return a single value. In Line Table functions return a single table variable that was created by a select statement. The final UDF is a Multi-statement Table Function. This function returns a table variable whose structure was created by hand, similar to a Create Table statement. It is useful when complex data manipulation inside the function is required.

Scalar UDFs
Our first User Defined Function will accept a date time, and return only the date portion. Scalar functions return a value. From inside Query Analyzer, enter:
  
CREATE FUNCTION dbo.DateOnly(@InDateTime datetime)
RETURNS varchar(10)
AS
BEGIN
        DECLARE @MyOutput varchar(10)
        SET @MyOutput = CONVERT(varchar(10),@InDateTime,101)
        RETURN @MyOutput
END
To call our function, execute: SELECT dbo.DateOnly(GETDATE())
Notice the User Defined Function must be prefaced with the owner name, DBO in this case. In addition, GETDATE can be used as the input parameter, but could not be used inside the function itself. Other built in SQL functions that cannot be used inside a User Defined Function include: RAND, NEWID, @@CONNCECTIONS, @@TIMETICKS, and @@PACK_SENT. Any built in function that is non-deterministic.
The statement begins by supplying a function name and input parameter list. In this case, a date time value will be passed in. The next line defines the type of data the UDF will return. Between the BEGIN and END block is the statement code. Declaring the output variable was for clarity only. This function should be shortened to:
  
CREATE FUNCTION testDateOnly(@InDateTime datetime)
RETURNS varchar(10)
AS
BEGIN
        RETURN CONVERT(varchar(10),@InDateTime,101)   
END
Inline Table UDFs
These User Defined Functions return a table variable that was created by a single select statement. Almost like a simply constructed non-updatable view, but having the benefit of accepting input parameters.
This next function looks all the employees in the pubs database that start with a letter that is passed in as a parameter. In Query Analyzer, enter and run:
  
USE pubs
GO
 
CREATE FUNCTION dbo.LookByFName(@FirstLetter char(1))
RETURNS TABLE
AS
RETURN SELECT *
FROM employee
WHERE LEFT(fname, 1) =  @FirstLetter
To use the new function, enter: 
SELECT * FROM dbo.LookByFName('A')
All the rows having a first name starting with A were returned. The return is a Table Variable, not to be confused with a temporary table. Table variables are new in SQL 2000. They are a special data type whose scope is limited to the process that declared it. Table variables are stated to have performance benefits over temporary tables. None of my personal testing has found this result though.
Multi Statement UDFs
Multi Statement User Defined Functions are very similar to Stored Procedures. They both allow complex logic to take place inside the function. There are a number of restrictions unique to functions though. The Multi Statement UDF will always return a table variable–and only one table variable. There is no way to return multiple result sets. In addition, a User Defined Function cannot call a Stored Procedure from inside itself. They also cannot execute dynamic SQL. Remember also, that UDFs cannot use non-deterministic built in functions. So GETDATE and RAND cannot be used. Error handling is restricted. RAISERROR and @@ERROR are invalid from inside User Defined Functions. Like other programming languages, the purpose of a User Defined Function is to create a stand-alone code module to be reused over and over by the global application.
For a Multi Statement test, we will create a modified version of the LookByFName function. This new function will accept the same input parameter. But rather than return a table from a simple select, a specific table will be created, and data in it will be manipulated prior to the return: 
CREATE FUNCTION dbo.multi_test(@FirstLetter char(1))
RETURNS @Result TABLE
        (
        fname varchar(20),
        hire_date datetime,
        on_probation char(1)
        )
AS
BEGIN
        INSERT INTO @Result
               (fname, hire_date)
               SELECT fname, hire_date
               FROM employee
               WHERE LEFT(fname, 1) =  @FirstLetter
        
        UPDATE @Result
        SET on_probation = 'N'
        
        UPDATE @Result
        SET on_probation = 'Y'
        WHERE hire_date < '01/01/1991'
        
        RETURN
END
To use the new function, execute: 
SELECT * FROM dbo.multi_test('A')
With the new Multi Statement Function, we can manipulate data like a Stored Procedure, but use it in statement areas like a View.
For example, only specific columns can be returned. 
SELECT fname FROM dbo.multi_test('A')
The function can also be joined like a view: 
SELECT e.lname, f.fname
FROM employee e INNER JOIN dbo.multi_test('A') f ON e.fname = f.fname

Comments

Post a Comment

Popular posts from this blog

Google to FTS Syntax Cheat Sheet

Image
OPERATOR EXAMPLE DESCRIPTION   nut Searches for inflectional forms of the word nut crank arm crank AND arm Searches for documents containing inflectional forms of the words crank AND arm crank and ann. The keyword AND is optional. tire OR air Searches for documents containing inflectional forms of the words tire or air, “reflector bracket” Performs a phrase search for the phrase "reflector bracket". hardware -bracket Searches for documents containing inflectional forms of the word hardware but not the word bracket. +clamp Searches for the word darn') without generating inflectional forms. ~seat Searches for thesaurus forms of the word seat Assemb* Searches for words that begin with the prefix assemb . <washer nut> Searches for documents that contain the words washer in close proximity to the word nut
Read more

@@rowcount

@@rowcount is a global variable reports the number of affected rows in the most recently executed statement. Just like @@error you need to save it in a local variable if you want to use the value later, since @@rowcount is set after each statement. Since with SET you can only assign variable at a time, you must use SELECT if you need to save both @@error and @@rowcount into local variables: SELECT @err = @@error, @rowc = @@rowcount (For this reason, I prefer to always use SELECT for variable assignment, despite Microsoft's recommendations to use SET.) In T-SQL it is not an error if, for instance, an UPDATE statement did not affect any rows. But it can of course indicate an error in your application, as it could be an error if a SELECT returns more that one row. For these situations, you can check @@rowcount and raise an error and set a return value, if @@rowcount is not the expected value.
Read more

Sql Index

Image
Indexes help us retrieve data from tables quicker. Let's use an example to illustrate this point: Say we are interested in reading about how to grow peppers in a gardening book. Instead of reading the book from the beginning until we find a section on peppers, it is much quicker for us to go to the index section at the end of the book, locate which pages contain information on peppers, and then go to these pages directly. Going to the index first saves us time and is by far a more efficient method for locating the information we need. The same principle applies for retrieving data from a database table. Without an index, the database system reads through the entire table (this process is called a 'table scan') to locate the desired information. With the proper index in place, the database system can then first go through the index to find out where to retrieve the data, and then go to these locations directly to get the needed data. This is much faster.
Read more