February 08, 2011

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.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

4 Responses to "SQL Server: Return Multiple Values from a Function"
  1. Nisha said...
    February 9, 2011 at 9:30 PM

    Madhivanan sir, is this also called a table-valued function that you have shown in this example?

  2. Madhivanan said...
    February 9, 2011 at 10:45 PM

    Nisha,

    Yes it is also known as table-valued function

  3. Anonymous said...
    February 10, 2011 at 1:53 AM

    Very nice query. I have a question. When will I use a table value function and when to use a Stored Procedure which returns one or more Output Parameters?

  4. Madhivanan said...
    February 10, 2011 at 2:44 AM

    Suppose you want to split the data and join with another table you can use a table-valued function. If you want to do lot of calculations(including updating or deleting data) and return only a value, you can use a stored procedure with output parameter

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions