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.
Madhivanan sir, is this also called a table-valued function that you have shown in this example?
ReplyDeleteNisha,
ReplyDeleteYes it is also known as table-valued function
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?
ReplyDeleteSuppose 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
ReplyDelete