SQL Server function return table

In this SQL Server tutorial, we will learn about the SQL Server function return table. We will illustrate this topic using multiple examples.

SQL Server function return table

In this section, we will learn how to create a user-defined function in SQL Server that returns table-type data. For this task, first, we need to know 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.

In SQL Server, a user-defined function can be classified into two different types. The first is the Scaler function, and the second is the table-valued function. Simply, 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 two types.

1. Inline Table-Valued Function
2. Multi-Statement Table Valued Function

Inline Table-Valued Function

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

Now, as it consists of only one statement, 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 the CREATE FUNCTION statement to create a user-defined function where function_name is the function’s name.

Next, we use the RETURNS TABLE statement to define the return data type for this function.

In the end, we are using the RETURN statement consisting 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 mainly used to read data from the database and perform 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 due to 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 

First, we define the name and parameters of the user-defined function. After this, we define the return type of this function, which is a table variable.

Next, we use 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 have a clear idea of how to create a table-valued function in SQL Server. Next, we can discuss some examples of creating different table-valued function types. For the example demonstration, consider the following customers’ table:

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

Example-1

In the first example, let’s understand how to create an inline table-valued function in SQL Server. 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 named “GetCustByCountry“. Now, this function takes a country name as a parameter and returns the records of that specified country.

To execute this function, we need to use the SELECT statement and 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 that are from the United States. The final result of the query is shown below.

Read SQL Server stored procedure return value

Example-2

In the second example, we will understand how to create a multi-statement table-valued function in SQL Server. 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.

To execute this function, we need to use the SELECT statement and 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 customer records from the United States and Canada. The final result of the query is shown below.

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.

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. First, consider the following tables.

For the example demonstration, we will use customer tables from different countries. One is the customer table of the United States and another is Canada’s customer table.

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. The function will fetch the table values from 2 different tables and insert them in the table variable.

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.

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 table type data.

In SQL Server, we can use the table-valued function to return table type data. A table-valued function is classified as one of the 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.

In the first section, we have already explained how to create different types of table-valued functions in SQL Server. 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 an SQL Server function that returns a table and how to use the declare variable option.

Now, we will understand the whole implementation using an example, and 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 declare the country_name variable. Then, we use the country_name variable to insert data into the table variable.

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

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.

Like many programming languages, SQL Server also supports the use of IF-ELSE block. An IF-ELSE statement in SQL Server controls 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. 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 on the country name passed as a parameter. Within the function, we are using the IF-ELSE block to check the country name. Further, based on the country name, an INSERT statement will be executed.

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

Read SQL Server Replace Function + Examples

SQL Server function return table from stored procedure

This section will explain 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. In contrast, 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 return 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

We simply need to execute the stored procedure to get the result as a table variable.

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 inner join is 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. For this example, consider the following two 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 created an inline table-valued function named “InnerJoinEx”. In the function, we use the inner join between the Customers and Orders table. Therefore, we will get the following result when we query this function.

Read Azure SQL database query history

SQL Server function return table dynamic sql

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

This section will explain how to use a table-valued function in the dynamic SQL statement. However, first, let’s know 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 will enable 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.

Read Bulk loading data to Azure SQL

SQL Server function return table with dynamic columns

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

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

Related SQL Server tutorials:

• Arithmetic operators in SQL Server
• SQL Operand data type real is invalid for modulo operator
• SQL Server scheduled stored procedure
• SQL Server stored procedure case statement
• Could not find stored procedure in SQL Server

So, in this tutorial, we have learned about the SQL Server function return table. And we have illustrated how to return table from function in SQL server using multiple examples.