0

I has a scalar valued function that takes an integer as one of it's input. I need a query that runs that function for the set of integers (1,2,3,4,5). This is what I have done so far:

SELECT dbo.MyFunction('2016-05-13', Number) 
FROM (SELECT TOP 5 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number FROM SomeTable) AS T

And this works. Is there a way I can do it without the FROM SomeTable since I don't actually use any information contained in the table?

Alternatively, is there a cleaner way to write the entire query?

Dan
  • 45,079
  • 17
  • 88
  • 157
  • 1
    It should be noted that your current query is ill-defined, in that if `SomeTable` contains more than 5 rows, there was *no* guarantee that you would, in fact, retrieve 5 rows with the numbers 1-5. – Damien_The_Unbeliever May 13 '16 at 12:35
  • Perhaps rethinking this process would be a better approach. Instead of using a scalar function (which are horribly inefficient), you could rethink this to be an inline table valued function that uses a tally table. It would be faster and more flexible than calling a scalar function over and over. If you can share the content of your function I will put something together to demonstrate. – Sean Lange May 13 '16 at 13:49
  • @SeanLange performance isn't really an issue here, I just need to pull these values into a spreadsheet once a day. Thanks anyway. – Dan May 13 '16 at 13:56

1 Answers1

1

Since SQL Server 2008, we have table value constructors:

SELECT dbo.MyFunction('2016-05-13', Number) 
FROM (VALUES (1), (2), (3), (4), (5)) AS T(Number)

If "5" is an arbitrary number and you need this to run over any sequential sequence, you will still need to use a nums table of some sort. Rather than repeat the various techniques for that here, I refer to this question and its answers.

Community
  • 1
  • 1
Jeroen Mostert
  • 27,176
  • 2
  • 52
  • 85
  • Perfect, that's what I was looking for. – Dan May 13 '16 at 12:27
  • Apologies for this add-on question, but if I want to use the result of `dbo.MyFunction` in a `WHERE` clause, do I need to nest that in an outer query? – Dan May 13 '16 at 12:32
  • If you really want to use the results in a `WHERE`, for starters, use an inline table-valued function. Scalar functions have atrocious performance. As an added bonus, when you `CROSS APPLY` those they result in a column that's easily filtered in a `WHERE`. – Jeroen Mostert May 13 '16 at 12:35
  • Thanks, so even if the function is only ever going to return a single value, I should still make it a table-valued function rather than a scalar-value one? Is there any advantage / point to scalar-values functions then? – Dan May 13 '16 at 12:39
  • They're simpler to write -- but unfortunately, that's about it, as long as the optimizer isn't overhauled. Fortunately, things are looking up for [in-memory OLTP](https://msdn.microsoft.com/library/dn935012), where they are actually fast. – Jeroen Mostert May 13 '16 at 12:52
  • One caveat I would add is that the performance benefit is almost always worse if your table valued function is a multi-statement table valued function. That means if there is more than a single select statement in the body. What happens in that case is it will be treated like a scalar function and the optimizer get confused and the performance is horrible. – Sean Lange May 13 '16 at 14:08