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
returns table as
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.