SQL Server function return table

In this SQL Server tutorial, we are going to learn about the SQL Server function return table. And we will illustrate this topic using multiple examples. Here is the list of topics that we are going to discuss.

SQL Server function return table
SQL Server function return table example
SQL Server function return table type
SQL Server function return table variable
SQL Server function return table with declare
SQL Server function return table dynamic sql
SQL Server function return table declare variable
SQL Server function return table with dynamic columns
SQL Server function return table from stored procedure
SQL Server function return table if else
SQL Server function return table inner join

Table of Contents

SQL Server function return table

In this section, we will learn how we can create a user-defined function in SQL Server that returns table type data. And for this task, first, we need to learn about user-defined functions in SQL Server.

Similar to most of the programming languages, SQL Server also supports the use of user-defined functions or commonly known as UDFs. The UDF functionality was introduced in SQL Server 2000, and it is available for all the SQL Server versions.

A user-defined function (UDF) is a programming construct that takes parameters, performs actions, and returns the result of those activities. Moreover, a user-defined function can be employed in scripts, stored procedures, triggers, and other UDFs.

Now, in SQL Server, a user-defined function can be classified into two different types. The first is the Scaler function and the second one is the table-valued function. In simple terms, a scalar user-defined function accepts zero or more parameters and returns a single value.

On the other hand, a table-valued UDF accepts zero or more parameters and returns a table variable. Moreover, it also allows users to query the result of the function. Now, a table-valued UDF in SQL Server can be further classified into 2 types.

Inline Table-Valued Function
Multi-Statement Table Valued Function

Inline Table-Valued Function

The inline table-valued function is a type of user-defined function that only consists of one statement.

Now, as it consists of only one statement then, this statement will be the SELECT statement and the query’s result is used as the function’s return value. And this type of function doesn’t require the use of the BEGIN-END block.

Now, let’s look at the syntax of creating an inline table-valued function in SQL Server.

CREATE FUNCTION function_name (parameters)  
RETURNS TABLE  
AS  
RETURN [ ( ] select_stmt [ ) ]

In the above syntax, first, we are using CREATE FUNCTION statement to create a user-defined function where function_name is the name of the function.

Next, we are using the RETURNS TABLE statement to define the return data type of this function.

In the end, we are using the RETURN statement which will consist of a SELECT statement.

Read Create a table from view in SQL Server

Multi-Statement Table-Valued Function

As its name depicts, multiple SQL statements are contained in BEGIN-END blocks in a Multi-Statement table-valued function. This type of function is mostly used to read some data from the database and then, perform some operations.

The return value of a Multi-Statement table-valued function is specified as a table variable and contains the entire structure of the table to be returned. Moreover, the declared table variable will be returned as a result of this function.

Now, let’s look at the syntax of creating a multi-statement table-valued function in SQL Server.

CREATE FUNCTION  function_name (parameters)  
RETURNS @return_variable TABLE 
<table_type_definition>
AS  
BEGIN  
  function_body 
  RETURN   
END

In the above, first, we are defining the name and parameters of the user-defined function. After this, we are defining the return type of this function which is a table variable.

Next, we are using the BEGIN-END block and within the block, we can define multiple SQL statements.

Check out, SQL Server stored procedure vs function

SQL Server function return table example

Now that we got a clear idea of how to create a table-valued function in SQL Server. Next, we can discuss some examples related to creating different types of a table-valued function. And for the example demonstration consider the following Customers table.

And from the image above, we can observe that the customer table carries data of customers from different countries like the United States, Canada, etc. And we will use this data further in the examples.

SQL Server function return table example-1

In the first example, let’s understand how to create an inline table-valued function in SQL Server. And the query for this example is as follows.

CREATE FUNCTION GetCustByCountry(@country VARCHAR(50))  
RETURNS TABLE  
AS  
RETURN  
    SELECT * FROM Customers WHERE country=@country

In the example, we are creating an inline table-valued function with the name “GetCustByCountry“. Now, this function takes a country name as a parameter and returns the records of that specified country.

Now, to execute this function, we need to use the SELECT statement and also specify the paramere value. Here is the query for this task.

USE [sqlserverguides]
GO
SELECT * FROM GetCustByCountry('United States')
GO

In the above query, we are using the function to return all the customer records which are from the United States. The final result of the query is shown below.

Read SQL Server stored procedure return value

SQL Server function return table example-2

Now, in the second example, we will understand how to create a multi-statement table-valued function in SQL Server. And the query for this example is as follows.

CREATE FUNCTION udfGetCustomers
( @country_1 VARCHAR(50), @country_2 VARCHAR(50) )
    RETURNS @customers TABLE (
        customer_name VARCHAR(50),
        city VARCHAR(50),
        country VARCHAR(50)
    )
AS
BEGIN
    INSERT INTO @customers
    SELECT customer_name, city, @country_1   
    FROM Customers WHERE country=@country_1;

    INSERT INTO @customers
    SELECT customer_name, city, @country_2   
    FROM Customers WHERE country=@country_2;
    RETURN;
END;

In the example, we are creating a multi-statement table-valued function with the name “udfGetCustomers“. Now, this function takes two country names as a parameter and returns all the records of that specified country.

Now, to execute this function, we need to use the SELECT statement and also specify the paramere values in the function. Here is the query for this task.

USE [sqlserverguides]
GO
SELECT * FROM udfGetCustomers('United States', 'Canada')

In the above query, we are using the function to return all the customer records from two countries: the United States and Canada. The final result of the query is shown below.

SQL Server function return table example-2

Read SQL Server stored procedure parameters

SQL Server function return table variable

In this section, we will learn how to create a function in SQL Server that can return a table variable.

Now, for this task, we can create a table-valued function in SQL Server, or to be more specific, we can use the multi-statement table-valued function.

By using the multi-statement table-valued function, we can return some data as a table variable. The syntax of using a multi-statement table-valued function is illustrated in the first topic.

Let’s discuss its usability using an example and for this, first, consider the following tables.

SQL Server function return table variable example-1 — **Customers_US** table

SQL Server function return table variable example-2 — **Customer_CA** table

For the example demonstration, we are going to use customer tables of different countries. One is the customer table of the United States and another one is the customer table of Canada.

And we will create a multi-statement table-valued function that will return a table variable containing values from both tables.

ALTER FUNCTION [dbo].[udfGetCustomers]()
    RETURNS @customers TABLE (
        customer_name VARCHAR(50),
        city VARCHAR(50),
        country VARCHAR(50)
    )
AS
BEGIN
    INSERT INTO @customers
    SELECT customer_name, city, 'United States'   
    FROM Customers_US;

    INSERT INTO @customers
    SELECT customer_name, city, 'Canada'   
    FROM Customers_CA;
    RETURN;
END;

In the above example, we have created a table variable named customers. And the function will fetch the table values from 2 different tables and insert them in the table variable.

And when we query the function, it will return the table variable containing records from both tables.

USE [sqlserverguides]
GO
SELECT * FROM udfGetCustomers()

Here is the final result of executing the function in SQL Server.

**SQL Server function return table variable**

Read Try catch in SQL Server stored procedure with examples

SQL Server function return table type

Next, in this section, we will understand how we can use a function in SQL Server to return data of table type.

In SQL Server, we can use the table-valued function to return data of table type. A table-valued function is classified as one of the types of user-defined functions in SQL Server that returns rowset as a result. Moreover, we can utilize a table-valued function as a table in SQL Server.

We have already explained how to create different types of table-valued functions in SQL Server in the first section. And examples related to a table-valued function are illustrated in the second section.

Read Stored procedure for search functionality in SQL Server

SQL Server function return table with declare variable

In this section, we will illustrate how to create a SQL Server function that returns a table and how to use declare variable option in it.

Now, we will understand the whole implementation using an example and we will use the Customers table from the second section.

CREATE FUNCTION GetUSCustomers()
RETURNS @T TABLE(
  customer_name VARCHAR(50),
  country VARCHAR(50)
  )
AS
BEGIN
  DECLARE @country_name VARCHAR(50)
  SET @country_name = 'United States'
  INSERT INTO @T
  SELECT customer_name, @country_name
  FROM Customers
  WHERE country=@country_name
  RETURN
END

In the above query, we have created a multi-statement table-valued function that will return data related to the United States.

In the function, we are using the BEGIN-END block and within the BEGIN-END block, we have declared the country_name variable. And then, we are using the country_name variable to insert data in the table variable.

Now, if we query this function, it will return the following output.

SQL Server function return table with declare variable

Read SQL Server Convert Function + Examples

SQL Server function return table if else

In this section, we will learn how to use the IF-ELSE statement in a table-valued function in SQL Server.

Similar to many programming languages, SQL Server also supports the use of IF-ELSE block. An IF-ELSE statement in SQL Server is a control statement that enables us to run or skip a statement block depending on a condition.

Now, let’s understand how we can use this IF-ELSE block within a table-valued function using an example. And again for this example, we will use the Customers table from the second section.

CREATE FUNCTION GetDataByCountry(@country VARCHAR(50))
RETURNS @tbl TABLE
(
    id INT,
	name VARCHAR(50),
    city VARCHAR(50),
    country VARCHAR(50),
	country_code VARCHAR(50)
) 
AS
BEGIN

    IF  (@country = 'United States')
         INSERT INTO @tbl SELECT * FROM Customers WHERE country=@country

    ELSE IF (@country = 'Canada')
         INSERT INTO @tbl SELECT * FROM Customers WHERE country=@country

    ELSE IF (@country = 'New Zealand')
         INSERT INTO @tbl SELECT * FROM Customers WHERE country=@country

    RETURN

END

Now, the above example will return the customer data based upon the country name passed as a parameter. And within the function, we are using the IF-ELSE block to check the country name. Further, based upon the country name an INSERT statement will be executed.

Let’s execute this function by taking an example of the “United States”.

SQL Server function return table if else

Read SQL Server Replace Function + Examples

SQL Server function return table from stored procedure

In this section, we will understand how to use a table-valued function in a SQL Server stored procedure. But, first, let’s tale an overview of the table-valued function and stored procedure in SQL Server.

A table-valued function in SQL Server is a special type of user-defined function that returns data of table type. Whereas a stored procedure in SQL Server is a collection of prepared SQL code that we can store and reuse over and over.

Now, let’s see how to use a table-valued function in a SQL Server stored procedure using an example. For the example illustration, first, we will create a table-valued function and then, we will create a stored procedure that uses the function.

ALTER FUNCTION udfGetCustomers
( @country VARCHAR(50) )
    RETURNS @customers TABLE (
        customer_name VARCHAR(50),
        city VARCHAR(50),
        country VARCHAR(50)
    )
AS
BEGIN
    INSERT INTO @customers
    SELECT customer_name, city, @country   
    FROM Customers WHERE country=@country;
    RETURN;
END;

The above function will take a country name as a parameter and returns the customer records of that country. Next, we will create a stored procedure that uses this function in its body. And the query for this is as follows.

CREATE PROC sp_GetCustomersByCountry
( @country VARCHAR(50) )
AS
BEGIN
  SELECT * FROM udfGetCustomers(@country)
END

Now, we simply need to execute the stored procedure to get the result as a table variable.

**SQL Server function return table from stored procedure**

Read SQL Server Substring Function

SQL Server function return table inner join

In this section, we will learn how to create a table-valued function on SQL Server that also uses inner join. But before understanding this implementation, let’s recall what is inner join in SQL Server.

A JOIN statement is used to bring rows from two or more tables together based on a common column. Moreover, the INNER JOIN statement picks records in both tables that have the same value.

Now, let’s understand the implementation using an example. And for this example, consider the following 2 tables.

Next, we will create an inline table-valued function that will perform an inner join on the Customers and Orders table.

CREATE FUNCTION InnerJoinEx()  
RETURNS TABLE  
AS  
RETURN  
    SELECT Customers.id, Customers.customer_name, Customers.country 
	FROM Customers 
	INNER JOIN Orders
	ON Customers.id=Orders.customer_id

In the above example, we have created an inline table-valued function with the name “InnerJoinEx“. In the function, we are using the inner join between the Customers and Orders table. Therefore, when we query this function, we will get the following result.

**SQL Server function return table inner join**

Read Azure SQL database query history

SQL Server function return table dynamic sql

Now, we cannot execute a dynamic SQL statement within a table-valued function in SQL Server. But, querying a table-valued function is similar to how we query a standard table in SQL Server, so we can use these functions as a dynamic SQL.

In this section, we will understand how to use a table-valued function in the dynamic SQL statement. However, first, let’s understand what is a Dynamic SQL statement in SQL Server.

Dynamic SQL is a programming approach that allows you to create SQL statements dynamically while they are being executed. Because the whole wording of SQL statements may be uncertain at compilation, it allows you to design more general-purpose and flexible SQL statements.

To understand this implementation, we will create a table-valued function in SQL Server. And then, we will use that function as a dynamic SQL statement.

CREATE FUNCTION GetCustomerData()  
RETURNS TABLE  
AS  
RETURN  
    SELECT * FROM Customers

By using the above query, we have created an inline table-valued function that will return records from the Customers table. Next, we will create a dynamic SQL statement and execute it using the sp_executesql procedure.

DECLARE @sql NVARCHAR(MAX)
    
SET @sql = N'SELECT * FROM GetCustomerData()';

EXEC sp_executesql @sql;

In the end, when we execute the dynamic SQL statement, we will get the following output.

SQL Server function return table dynamic sql — SQL Server function return table dynamic SQL

Read Bulk loading data to Azure SQL

SQL Server function return table with dynamic columns

In SQL Server, a table-valued function comes with many restrictions and it will require static column names. This is because we need to define the columns and their types while using a table-valued function. And defining column type could be an issue while implementation.

The best way is to use the stored procedures or dynamic SQL statements to define dynamically a column in SQL Server.