SQL Server

User-Defined Functions in SQL Server

Introduction

 
In this blog, I will explain the SQL user-definable functions. User-defined functions accepts 0 or more parameters and return a single data value or table data type.
 

Types

 
  1. Scalar valued function
  2. Table valued function
        a. Inline table-valued function
        b. Multi statement table-valued function
 
 
 

Scalar Function

 
A scalar user-defined function returns one of the scalar data types. It can accept 0 or more parameters and return a single value. Text, ntext, images and timestamp data types are not supported. Scalar functions can be used in a WHERE clause of the SQL query.
 
Syntax
  1. CREATE FUNCTION FN_GETFULLNAME(@FNAME NVARCHAR(100),@LANME NVARCHAR(100))  
  2. RETURNS NVARCHAR(200)  
  3. AS  
  4. BEGIN  
  5. RETURN (SELECT @FNAME + ' ' + @LANME AS 'FULLNAME');  
  6. END;  
  7.   
  8. SELECT DBO.FN_GETFULLNAME('MAGESH','R');-- EXECUTE  

Table-valued Function

 
A table-valued function is a user-defined function that accepts 0 or more parameters and returns data of a table type.
  1. Inline table-valued function
  2. Multi statement table-valued function
 

Inline-Table Valued Function

 
An inline-table valued Function can only return a single value. There is no need for a BEGIN-END block.
 
Syntax
  1. CREATE FUNCTION FN_TABLE()  
  2. RETURNS TABLE  
  3. AS  
  4. RETURN (SELECT * FROM DETAILS);  
  5.   
  6. SELECT * FROM DBO.FN_TABLE()—EXECUTE  

Multi-Statement Function

 
The Multi-Statement table-valued function returns table value and multiple SQL statements enclosed in BEGIN-END blocks. The return statement doesn't have a value.
 
Syntax
  1. CREATE FUNCTION FN_MTABLE()  
  2. RETURNS @EMP TABLE(  
  3. SNO INT,  
  4. SNAME NVARCHAR(100)  
  5. )  
  6. AS  
  7. BEGIN  
  8. INSERT INTO @EMP VALUES(1,'TEST');  
  9. RETURN  
  10. END;  
  11. SELECT * FROM DBO.FN_MTABLE()  
Membership not found