User-Define Function And Types

Question

Hi friends I want to know what is User-define function ? and types of user define function with example. thank you.

Anil Kumar · Answer

@Ajay: hope your problem would be solved @Satyapriya: tnx for sharing good info

Senthilkumar · Answer

Hi Ajay The user defined function in sql server used to do the repeat statement execution in a function where it can be cached changes in one position and can call any where in the sql server statement. There are three types. Scalar Inline Table-Valued and Multi-statement Table-valued. For more info: http://www.sqlteam.com/article/user-defined-functions

Satyapriya Nayak · Answer

Hi Ajay User-Defined Functions User-defined functions are created using the CREATE FUNCTION statement modified using the ALTER FUNCTION statement and removed using the DROP FUNCTION statement. Each fully qualified user-defined function name ( database_name . owner_name . function_name ) must be unique. You must have been granted CREATE FUNCTION permissions to create alter or drop user-defined functions. Users other than the owner must be granted appropriate permissions on a function before they can use it in a Transact-SQL statement. To create or alter tables with references to user-defined functions in the CHECK constraint DEFAULT clause or computed column definition you must also have REFERENCES permission on the functions. Transact-SQL errors that cause a statement to be canceled and continue with the next statement in the module (such as triggers or stored procedures) are treated differently inside a function. In functions such errors cause the execution of the function to stop. This in turn causes the statement that invoked the function to be canceled. Types of User-Defined Functions SQL Server 2000 supports three types of user-defined functions: Scalar functions Inline table-valued functions Multistatement table-valued functions A user-defined function takes zero or more input parameters and returns either a scalar value or a table. A function can have a maximum of 1024 input parameters. When a parameter of the function has a default value the keyword DEFAULT must be specified when calling the function to get the default value. This behavior is different from parameters with default values in stored procedures in which omitting the parameter also implies the default value. User-defined functions do not support output parameters. Scalar functions return a single data value of the type defined in a RETURNS clause. All scalar data types including bigint and sql_variant  can be used. The timestamp data type user-defined data type and nonscalar types such as table or cursor  are not supported. The body of the function defined in a BEGIN...END block contains the series of Transact-SQL statements that return the value. The return type can be any data type except text  ntext  image  cursor  and timestamp . Table-valued functions return a table . For an inline table-valued function there is no function body; the table is the result set of a single SELECT statement. For a multistatement table-valued function the function body defined in a BEGIN...END block contains the TRANSACT-SQL statements that build and insert rows into the table that will be returned. For more information about inline table-valued functions see Inline User-Defined Functions . For more information about table-valued functions see User-Defined Functions That Return a table Data Type . The statements in a BEGIN...END block cannot have any side effects. Function side effects are any permanent changes to the state of a resource that has a scope outside the function such as a modification to a database table. The only changes that can be made by the statements in the function are changes to objects local to the function such as local cursors or variables. Modifications to database tables operations on cursors that are not local to the function sending e-mail attempting a catalog modification and generating a result set that is returned to the user are examples of actions that cannot be performed in a function. The types of statements that are valid in a function include: DECLARE statements can be used to define data variables and cursors that are local to the function. Assignments of values to objects local to the function such as using SET to assign values to scalar and table local variables. Cursor operations that reference local cursors that are declared opened closed and deallocated in the function. FETCH statements that return data to the client are not allowed. Only FETCH statements that assign values to local variables using the INTO clause are allowed. Control-of-flow statements. SELECT statements containing select lists with expressions that assign values to variables that are local to the function. UPDATE INSERT and DELETE statements modifying table variables that are local to the function. EXECUTE statements calling an extended stored procedure. The number of times that a function specified in a query is actually executed can vary between execution plans built by the optimizer. An example is a function invoked by a subquery in a WHERE clause. The number of times the subquery and its function is executed can vary with different access paths chosen by the optimizer. Built-in functions that can return different data on each call are not allowed in user-defined functions. The built-in functions not allowed in user-defined functions are: @@CONNECTIONS @@PACK_SENT GETDATE @@CPU_BUSY @@PACKET_ERRORS GetUTCDate @@IDLE @@TIMETICKS NEWID @@IO_BUSY @@TOTAL_ERRORS RAND @@MAX_CONNECTIONS @@TOTAL_READ TEXTPTR @@PACK_RECEIVED @@TOTAL_WRITE Schema-Bound Functions CREATE FUNCTION supports a SCHEMABINDING clause that binds the function to the schema of any objects it references such as tables views and other user-defined functions. An attempt to alter or drop any object referenced by a schema-bound function fails. These conditions must be met before you can specify SCHEMABINDING in CREATE FUNCTION: All views and user-defined functions referenced by the function must be schema-bound. All objects referenced by the function must be in the same database as the function. The objects must be referenced using either one-part or two-part names. You must have REFERENCES permission on all objects (tables views and user-defined functions) referenced in the function. You can use ALTER FUNCTION to remove the schema binding. The ALTER FUNCTION statement should redefine the function without specifying WITH SCHEMABINDING. Calling User-Defined Functions When calling a scalar user-defined function you must supply at least a two-part name: SELECT * MyUser.MyScalarFunction() FROM MyTable Table-valued functions can be called by using a one-part name: SELECT * FROM MyTableFunction() However when you call SQL Server built-in functions that return a table you must add the prefix :: to the name of the function: SELECT * FROM ::fn_helpcollations() A scalar function can be referenced any place an expression of the same data type returned by the function is allowed in a Transact-SQL statement including computed columns and CHECK constraint definitions. For example this statement creates a simple function that returns a decimal: CREATE FUNCTION CubicVolume -- Input dimensions in centimeters (@CubeLength decimal(41) @CubeWidth decimal(41) @CubeHeight decimal(41) ) RETURNS decimal(123) -- Cubic Centimeters. AS BEGIN RETURN ( @CubeLength * @CubeWidth * @CubeHeight ) END This function can then be used anywhere an integer expression is allowed such as in a computed column for a table: CREATE TABLE Bricks ( BrickPartNmbr int PRIMARY KEY BrickColor nchar(20) BrickHeight decimal(41) BrickLength decimal(41) BrickWidth decimal(41) BrickVolume AS ( dbo.CubicVolume(BrickHeight BrickLength BrickWidth) ) ) dbo.CubicVolume is an example of a user-defined function that returns a scalar value. The RETURNS clause defines a scalar data type for the value returned by the function. The BEGIN...END block contains one or more Transact-SQL statements that implement the function. Each RETURN statement in the function must have an argument that returns a data value that has the data type specified in the RETURNS clause or a data type that can be implicitly converted to the type specified in RETURNS. The value of the RETURN argument is the value returned by the function. Obtaining Information About Functions Several catalog objects report information about user-defined functions: sp_help reports information about user-defined functions. sp_helptext reports the source of user-defined functions. Three information schema views report information about user-defined functions: ROUTINES  PARAMETERS  and ROUTINE_COLUMNS . These information schema views also report information for stored procedures. Please refer the below links http://msdn.microsoft.com/en-us/library/aa175085%28v=sql.80%29.aspx http://www.sqlteam.com/article/user-defined-functions http://www.sqlteam.com/article/intro-to-user-defined-functions-updated Thanks