0

I'm a bit confused about what is better to use in the following case: I have a quite complex query that is used for reporting purposes. A simplified version looks like 

  SELECT [type], COUNT(*) as total_num, COUNT(DISTINCT user_id) as uq_user_num
  FROM table1
  INNER JOIN table2 ON (...)
  ...
  WHERE table3.last_action_date BETWEEN :start_date AND :end_date
  GROUP BY [type]

I can create an inline function or a stored procedure that takes start_date and end_parameters and executes this query.
I incline to function because this task does not involve any data modification or complex logic. Also, I may want to use the result in APPLY later (it's not really important at the moment).
Does it make sense to use function, not procedure? Is it any difference from performance point of view (execution plan caching, etc) ?

Thank you.

a1ex07
  • 36,826
  • 12
  • 90
  • 103

2 Answers2

1

Using a multi-statement table valued function is similar to using a proc from plan caching and cached plan reuse perspective.. Using an inline table valued function is similar to using a view from the plan cache and plan reuse perspective(reuse only happen is exact same statement is used. ie same parameters). Considering the same you should use a multi-statement table valued function.

sqlwithpanks
  • 96
  • 1
  • 3
0

You may want to consider using a View too. A view is efficient if the results do no change given the parameters provided, which is what you have here. In this case, the results will not change if you make two calls with the same start and end date.

However, two of the main differences between a stored proc and a function are that you cannot call updates/ inserts from a function and you cannot call a stored proc as part of a select statement, but you can with a function.

See this thread for more info:

Function vs. Stored Procedure in SQL Server

Community
  • 1
  • 1
Digbyswift
  • 10,310
  • 4
  • 38
  • 66
  • View won't work - I need to filter results and then group them. As far as I know, there is no way I can pass parameters to view – a1ex07 Jun 19 '11 at 14:28
  • 1
    I saw that link before I posted my question; it has good information about difference between functions and procedures, but it's a bit too general. Also, at the moment, I don't really care about calling procedure inside `SELECT`; if I need to do so, I can always declare a table variable, then populate it with `INSERT INTO ... EXECUTE`, and then use the variable. – a1ex07 Jun 19 '11 at 14:38