Passing Table to a Function Parameter in SQL Server 2012

In this article, I describe how to pass a table to a function parameter in SQL Server.

In this article, I described how to pass a table to a function parameter in SQL Server. In this article, you create a table, student, then create a user-defined table type and pass the table type as a parameter to a function. So let's have a look at a practical example of how to pass a table as a function parameter in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio. 
 
Here is how to implement passing a user-defined table type to a function.
 

1. Create a Student Table in SQL Server

 
Create a table named Student.
  1. CREATE TABLE [dbo].[Student]  
  2. (  
  3.     [StudentID] [intNULL,  
  4.     [StudentName] [varchar](30) NULL,  
  5.     [StudentFees] [intNULL  
  6. )  

2. Create a User-Defined Table Type in SQL Server

 
Now create a user-defined table type to be used as a table-valued parameter in the function.
  1. CREATE TYPE dbo.StudentType AS TABLE  
  2. (  
  3.    [StudentID] [int] ,  
  4.    [StudentName] [varchar](30) ,  
  5.    [StudentFees] [int]   
  6. )  
Now Press F8 to see the created type in the Object Explorer.
 
Database->Programmability->Types->User Define Table Types
 
User-Defined Table Type in SQL Server 
 

3. Creating a Function in SQL Server

 
Now create the StudentDetailFunctionFunction. This function will accept a table-valued parameter.
 
READONLY keyword - This keyword is required to declare a table-valued parameter.
  1. ALTER FUNCTION StudentDetailFunction( @StudentDetail dbo.StudentType READONLY )  
  2. RETURNS VARCHAR(50)  
  3. AS  
  4. BEGIN  
  5.     DECLARE @Studentname VARCHAR(50)  
  6.     SELECT  @Studentname= StudentName FROM @StudentDetail  
  7.     RETURN @Studentname  
  8. END  
  9.   
  10.    

4. Execute the SQL Server Function

 
Now you can declare a variable @StudentVariable which contains the value of the table columns.
  1. DECLARE @StudentVariable AS StudentType  
  2. INSERT INTO @StudentVariable(StudentName) VALUES('Rohatash')  
  3. SELECT dbo.StudentDetailFunction(@StudentVariable)   
Output
 
Execute SQL Server Function