Function in SQL with Example

Function in SQL with Example

In SQL function is a Stored Program that you can pass parameters into and returns a value. 

A function must always return a value & but a procedure may or may not return a value.

Example :- (Return a single value from a Customer Table.)
------------------------------------------------
Create table Customer
 (
   SrNo  int identity(1,1) primary key,
   Name Varchar(50),
   Age  varchar(50),
   ContactNo varchar(50)
)
------------------------------------------------

Create Function 
----------------------------------------------

Create FUNCTION FUN1
(
-- Add the parameters for the function here
@id int
)
returns varchar(50)
as
BEGIN

-- Declare the return variable here
DECLARE @Name varchar(50)

-- Add the T-SQL statements to compute the return value here
SELECT @name=Name from Customer WHERE SrNo= @id

-- Return the result of the function
Return  @name


END
----------------------------------------------


Execute Function
----------------------------------------------
select dbo.fun1(1)
----------------------------------------------

Output/Result :-