SQL Server Functions

Functions : Creates a user-defined function, which is a saved Transact-SQL routine that returns a value. User-defined functions cannot be used to perform a set of actions that modify the global database state. User-defined functions, like system functions, can be invoked from a query. They also can be executed through an EXECUTE statement like stored procedures.
Types Of Functions:

1. Row set functions :Can be used like table references in an SQL statement.
2. Aggregate functions :Operate on a collection of values but return a single, summarizing value.
3. Scalar functions :Operate on a single value and then return a single value. Scalar functions can be used wherever an expression is valid. This table categorizes the scalar functions.

SQL Server Function Categories:

1. Configuration Functions:Returns information about the current configuration.
2. Cursor Functions:Returns information about cursors.
3. DateandTimeFunctions:DATEADD ,DATEDIFF,DATENAME,DATEPART,DAY ,GETDATE ,GETUTCDATE ,MONTH,YEAR
   Performs an operation on a date and time input value and returns either a string, numeric, or date and time value.
4. Mathematical Functions:performs a calculation based on input values provided as parameters to the function, and returns a numeric value.
5. Metadata Functions:Returns information about the database and database objects.
6. Security Functions:Returns information about users and roles.
7. String Functions:Performs an operation on a string (char or varchar) input value and returns a string or numeric value.
8. System Functions:Performs operations and returns information about values, objects, and settings in Microsoft SQL Server.
9. System Statistical Functions:Returns statistical information about the system.
10. Text and Image Functions: Performs an operation on a text or image input values or column, and returns information about the value.

Functions Behaviour:UDF's support a wide range of functionality and just because you can’t commit write changes to a database, it doesn't mean that you should turn a blind eye to them. Here's a list of the statements that you can include in the body of a UDF

• Flow control statements such as IF…ELSE, CASE, GOTO labels, etc.
• UPDATE, INSERT and DELETE commands to modify the contents of tables which are local to the UDF and have been created using SQL Server 200's news TABLE variable type.
• The EXECUTE command to execute stored procedures and extended stored procedures.
• The SET and SELECT statements to assign a value to a variable.
• SELECT statements to retrieve field values from tables into local variables.
• CURSOR, OPEN CURSOR, CLOSE CURSOR, DEALLOCATE CURSOR and FETCH NEXT FROM statements as long as the cursor is both created and disposed of within the scope of the UDF. Note that actual rows can't be returned, and they must be explicitly returned into type-matching variables using the FETCH NEXT INTO statement.
• DECLARE statements to create variables local (in terms of scope) to the UDF.
  Internally defined SQL variables which are prepended with "@@", such as @@ERROR and @@FETCH_STATUS.

Function and Stored Procedures Comparision:

• Scope of modification: UDF's aren't allowed to modify the physical state of a database using INSERT, UPDATE or DELETE statements. They can only work with local data.
• Output parameters: UDF's don't have the ability to return output parameters to the calling function. They do however let us return a single scalar value or a locally created table.
• Error handling: In UDF's, if an error occurs during a call to a stored procedure or a trigger, then that statement is terminated and flow control continue through the UDF.
• Calling conventions: One of the major differences between UDF's and stored procedures is that a UDF can be called through a SQL statement without using the EXECUTE statement

Creating Scalar Function:

CREATE FUNCTION dbo.mult(@num1 INT,@num2 INT)

RETURNS INT

AS

BEGIN

RETURN (@num1 * @num2)

END

Creating Table Valued Function :

CREATE FUNCTION getAuthorsByState(@state CHAR(2) )

RETURNS TABLE

AS

RETURN(SELECT au_fname + ' ' + au_lname AS NameFROM authorsWHERE state = @state)