Introduction:

In this tutorial we will learn how  create Multi-Statement table valued function in SQL server with example

Description:

A Multi-Statement Table-Valued user-defined function return result as a table. It can have a single or multiple T-SQL statement. When you create a Multi - Statement Table-Valued function, You must define the table structure that is being returned result. You can also use the SQL Data Manipulation Language (DML) Statements INSERT, UPDATE, DELETE to create and manipulate the return results in the table variable that will be returned as the result.

Create Multistatement Table-Valued Functions. 

CREATE FUNCTION dbo.GetEmp(@mobile nvarchar(100))
RETURNS @result table
(
Id int primary key,
Fname nvarchar(100) NULL,
Mname nvarchar(100) NULL,
Lname nvarchar(100) NULL,
Mobile nvarchar(100) NULL,
EmailId nvarchar(100) NULL
 
)
AS
BEGIN
INSERT INTO @result
(
Id,
Fname,
Mname,
Lname,
Mobile,
EmailId
)
SELECT
Id,
Fname,
Mname,
Lname,
Mobile,
EmailId
FROM Employee where mobile like '%'+@mobile+'%'
RETURN;
END
;


Use a Multistatement Table-Valued Functions.  

SELECT * FROM
[ArticlesTesting].[dbo].[GetEmp] (8)

OutPut:

Multistatement Table-Valued Functions in SQL Server

Thanks.

2 Comments

  1. SQL server Multi-Statement table valued function Aditya Patel
    7/13/2018 10:28:00 PM

    SQL server Multi-Statement table valued function read on 

    https://www.mindstick.com/Articles/13001/multi-statement-table-valued-user-defined-function-in-sql-server


  2. By:priyanka patel priyanka patel
    6/1/2019 5:16:27 AM

    nice article.



Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.

You may also like