My Profile
Gifts
Active Members
TodayLast 7 Days
more...
|
An Indepth coverage on User-Defined Functions
Posted Date: 20 Mar 2005 Resource Type: Articles Category: Databases
|
Posted By: Vijaya Member Level: Diamond
Rating:  Points: 15
|
User-defined Functions
You must be wondering when we have Stored procedure why do we go for user-defined functions. Well the simple reason is we can’t call stored procedure as part of an expression. And we can’t even call the stored procedure in SELECT clause. That’s the reason Microsoft has introduced this feature in 2000. Now let’s dig into more details.
There are three types of user-defined functions in SQL Server. These are classified based on their return type.
1. Scalar Valued function
Returns a single value of any t-sql data type.
2. Simple Table Valued Function
Returns a table that’s based on a single SELECT statement.
3. Multi-statement table-valued function
Returns a table that’s based on multiple statements.
Like stored procedure a function can accept one or more input parameters. Function name is typically prefixed with function to uniquely identify it from other programs and it can be up to 128 characters. Function always returns a value. A function can’t have permanent effect on the database. It means it should not contain any DML statements inside. How ever inside of the function you can create a table, a temporary table, or a table variable. Then the function can perform insert, update, and delete operation on that table.
The most important thing about the function is mention the database owner of the function when invoking it.
Now lets more explore about different types of functions.
Scalar Valued Function
Let’s create a sample function which returns author id by passing last name.
CREATE FUNCTION fnAuthorID(@ln_name varchar(50))
RETURNS int
BEGIN
RETURN (SELECT au_id FROM Authors WHERE ln_name = @ln_name)
END
To invoke the above function in as part of SQL statement
SELECT fn_name, ln_name, au_id
FROM Authors
WHERE au_id = dbo.fnAuthorID(‘vijay’)
However UDF can’t be defined with output parameters instead the RETURN statement must be used to pass a value back to the calling environment. The value of type what you are returning from a function must match the type mentioned in function’s header.
This is the complete syntax to create a function.
CREATE FUNCTION [owner_name.]function_name
([@parameter_name data_type [= default]] [,….])
RETURNS data_type
[WITH {ENCRYPTION|SCHEMABINDING| ENCRYPTION, SCHEMABINDING}]
[AS]
BEGIN
[sql_statements]
RETURN scalar_expression
END
The CREATE FUNCTION clause names the function and declares the input parameters. If you don’t specify the database owner name as part of the name the function is created for the current database owner. To invoke a function that has parameters, you must pass the parameters by position. You can’t pass by name as you do it when you call a stored procedure. Furthermore you can’t simply omit the optional parameters when invoking a function as you could with a stored procedure. Instead you must use DEFAULT keyword as a placeholder for the optional parameter.
Functions can be defined with from zero to 1024 input parameters. The WITH SCHEMABINDING clause binds the function to the database schema. This prevents you from dropping or altering tables or views that are used by the function. As you know the WITH ENCRYPTION prevents the user to view the source code of the function.
Table-valued Function
Table valued function syntax will be slightly different when compared with scalar function. In this case we are returning a table to the calling environment so we have to mention table as return type in function header clause.
Now let’s look at the syntax.
CREATE FUNCTION [owner_name.]function_name
([@parameter_name data_type [= default]] [,….])
RETURNS TABLE
[WITH {ENCRYPTION|SCHEMABINDING| ENCRYPTION, SCHEMABINDING}]
[AS]
RETURN [(] select_statement [)]
To declare the function as table-valued you code the table type in the RETURNS clause. Then you code the SELECT statement that defines the table in parentheses in the RETURN statement.
A statement that creates a simple table-valued function
CREATE FUNCTION fnGetAuthors(@ln_name varchar(50))
RETURNS table
BEGIN
RETURN (SELECT au_id,ln_name,fn_name FROM Authors WHERE ln_name = @ln_name)
END
Statement that invokes the above function
SELECT * FROM dbo.fnGetAuthors(‘vijay’)
A table valued function like the one shown in here acts like a dynamic view. To use a simple table-valued function code the function name in place of a table name or a view name. if you use a table-valued function in a join operation, you will want to assign a correlation name to it as shown above.
Multi-statement table-valued function
To create a multi statement table valued function creates a table variable in the RETURNS clause of the CREATE FUNCTION statement that defines the columns in the table. then code the statements that create the table within a BEGIN…END block. Code a RETURN statement within this block to return the table to the calling program.
Let’s look at the syntax for creating it.
CREATE FUNCTION [owner_name.]function_name
([@parameter_name data_type [= default]] [,….])
RETURNS @return_variable TABLE
(column_name_1 data_type [column_attributes]
[, (column_name_2 data_type [column_attributes]]…)
[WITH {ENCRYPTION|SCHEMABINDING| ENCRYPTION, SCHEMABINDING}]
[AS]
BEGIN
Sql_statements
RETURN
END
You code the SQL statements that create the table variable within a BEGIN..END block. This blocks ends with a RETURN keyword with no argument. This terminates the function and returns the table variable to the invoking program.
Statement that creates a multi statement table valued function
CREATE FUNCTION fnGetAuthors(@ln_name varchar(50))
RETURNS @OutTable table
(au_id int,fn_name varchar(50), ln_name varchar(50))
BEGIN
INSERT INTO @OutTable
SELECT au_id, fn_name, ln_name
FROM Authors
WHERE ln_name = @ln_name
END
To invoke the above function
SELECT mAuthors.au_id, mAuthors.ln_name, mAuthors.fn_name
FROM dbo. fnGetAuthors(‘vijay’) as mAuthors
Modify Function definition
To delete a user-defined function from the database, use the DROP FUNCTION statement. To modify the definition of a function you can delete the function and then create it again, or you can use the ALTER FUNCTION statement to specify the new definition. When you delete a function any security permissions that are assigned to the function and any dependencies between the function and the tables and views it uses are also deleted. In that case you will want to use the ALTER FUNCTION statement to modify the function and preserve the permissions and dependencies.
The syntax of the DROP FUNCTION statement
DROP FUNCTION [owner_name.]function_name[,….]
The syntax of the ALTER FUNCTION statement for a scalar valued function.
ALTER FUNCTION [owner_name.]function_name
([@parameter_name data_type [= default]] [,….])
RETURNS data_type
[WITH {ENCRYPTION|SCHEMABINDING| ENCRYPTION, SCHEMABINDING}]
[AS]
BEGIN
[sql_statements]
RETURN scalar_expression
END
Summary
So Now you can minimize the functionality in SQL statements and use functions where applicable.
|
Responses
|
| Author: Vijayakumaran 06 Jun 2005 | Member Level: Silver Points : 0 | hi !
all your articles are good. The way, you are choosing a topic and giving some indepth knowlodge into that is really appreciable.
Good effort.
Hope you will cover all the concepts in SQL Server, in this approach.
Thanks
Vijay
| | Author: Choudhary G. 29 Aug 2005 | Member Level: Bronze Points : 0 | Hi,
Vijay
you are doing very well job means every article provide
suficient information to learn more things
regards,
Gchoudhary
| | Author: Sandhya 29 Jul 2008 | Member Level: Bronze Points : 1 | Hi vijaya,
You are too gud, You are really provindg valuable infomration on each and every topic in SQL Server.
Hope to see many more things...
You are really setting a new way to me to lean on SQL Server....
I want to be your student...
Regards
Sandhya
|
|