A user-defined function is, much like a stored procedure , an ordered set of T-SQL statements that are pre-optimized and compiled and can be called to work as a single unit. The primary difference between them is how results are returned.

Basic Syntax,

CREATE FUNCTION [owner_name.]function_name
( [ <@parameter name> <scalar data type> [ = <default value>]
[ ,...n ] ] )
RETURNS {<scalar type>|TABLE}
[ WITH {ENCRYPTION|SCHEMABINDING|}]
AS
BEGIN
[<function statements>]
{RETURN <type as defined in RETURNS clause>|RETURN (<SELECT statement>)}
END

We can differentiate User Defined function’s in the following two ways,

User defined function’s-Returned a value.
User defined function’s-Returned a Table.

User defined function’s-Returned a value

CREATE FUNCTION dbo.DayOnly(@Date datetime)
RETURNS varchar(12)
AS
BEGIN
RETURN CONVERT(varchar(12), @Date, 101)
END

SELECT *
FROM Orders
WHERE dbo.DayOnly(OrderDate) = dbo.DayOnly(GETDATE())

User defined function’s-Returned a Table

USE pubs
GO

CREATE FUNCTION dbo.fnAuthorList()
RETURNS TABLE
AS
RETURN (SELECT au_id,
au_lname + ‘, ‘ + au_fname AS au_name,
address AS address1,
city + ‘, ‘ + state + ‘ ‘ + zip AS address2
FROM authors)
GO

SELECT *
FROM dbo.fnAuthorList()

USE pubs
GO

CREATE FUNCTION dbo.fnSalesCount(@SalesQty bigint)
RETURNS TABLE
AS
RETURN (SELECT au.au_id,
au.au_lname + ‘, ‘ + au.au_fname AS au_name,
au.address,
au.city + ‘, ‘ + au.state + ‘ ‘ + zip AS Address2
FROM authors au
JOIN titleauthor ta
ON au.au_id = ta.au_id
JOIN sales s
ON ta.title_id = s.title_id
GROUP BY au.au_id,
au.au_lname + ‘, ‘ + au.au_fname,
au.address,
au.city + ‘, ‘ + au.state + ‘ ‘ + zip
HAVING SUM(qty) > @SalesQty
)
GO

SELECT *
FROM dbo.fnSalesCount(25)

frankly,in performance wise sp is better than USD.

But while executing we can,t use select in sp’s.

Now i hope you got some idea about user defined functions.

Happy Coding.