SQL Server: Return Multiple Values from a Function

A SQL Server function can return a single value or multiple values. To return multiple values, the return type of the the function should be a table.

Let’s see an example where you want to pass a number to a SQL Server function and get consecutive date values

Create the following function

Here’s the same query to try out:

CREATE function dbo.test_function
(
@num int
)
returns table as
return
(
select number, dateadd(day,number-1,getdate()) as dates from master..spt_values
where type='p' and number between 1 and @num
)

Now call this function using the following statement:

select * from dbo.test_function(10)

Running the query will list out 10 consecutive dates starting from today, as shown below:

As you can see, the return type of the function test_function is a table. The table master..spt_values used in the function is a system table and the number column has numbers from 0 to 2047. We can make use of this system table as a number table, as we have done above. Alternatively you can also create a number table with large number of values and use it in the query.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan