User Defined Functions

Types of User Defined Functions
I. Scalar Functions
II. In-Line Table Functions
III. Multistatement Table Functions

Accept parameters of any scalar data type except rowversion

Scalar Functions	In-Line Table Functions	Multistatement Table Functions
Returns only scalar value.	Return a table variable that was created by a single select statement.	Returns a table variable. No multi result set. This type of function is used when we want to apply more logic except we can express in a single query. I.e. like stored procedure we can manipulate with more statements.
Support multiple T-SQL statements.	Supports only sing T-SQL Statement.	Support multiple T-SQL statements.
Return type is fixed.	No need to specify the table column names and its type.	We have to specify the table columns.
CREATE FUNCTION Testscalar(@ID INT) RETURNS INT AS BEGIN SELECT @ID = 10 SELECT @ID = @ID + 1 SELECT @ID = @ID + 1 RETURN @ID END	CREATE FUNCTION [dbo].[Testinline] ( Params ) RETURNS TABLE AS RETURN SELECT * FROM dbo.treeview CREATE FUNCTION [dbo].[Testinline](@i INT) RETURNS TABLE AS RETURN SELECT @i AS [ColName]	CREATE FUNCTION [dbo].[Testmultiline] ( @ID INT ) RETURNS @TableVariable TABLE ( I INT, J VARCHAR(10)) AS BEGIN INSERT INTO @TableVariable SELECT 1, '10' UNION ALL SELECT 2, '20' UNION ALL SELECT 3, '30' RETURN END
The last statement included within a function must be a return statement RETURN statements in scalar valued functions must include an argument. Simply retrun statemet is wrong.	The last statement included within a function must be a return statement. Return @I is wrong Return @I as a is wrong	The last statement included within a function must be a return statement. return @TableVariable is wrong.

Original Link : 15seconds

Valid

· Control-flow statements
· Assignment statements
· Variable declarations
· SELECT statements that modify local variables
· Cursor operations that fetch into local variables
· INSERT, UPDATE, DELETE statement that act upon local table variables
Invalid:

· Built-in, nondeterministic functions such as GetDate()
· Statements that update, insert, or delete tables or views
· Cursor fetch operations that return data to the client
. can’t able to execute a stored procedure.

How to Call Scalar-valued Functions

SELECT ColNames, dbo.ScalarValuedFunction(starttime,endtime) AS ColName FROM TableName
WHERE BankerID = dbo.ScalarValuedFunction('IBM')

How to Call Table-valued Functions

SELECT * FROM dbo.TableValuedFunctions('key1|key2|key3|key4|key5', '|')
-- For converting arrays to table

SELECT * FROM Department D 
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) 

SELECT * FROM Department D 
OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)