SQL User Defined Function Within Select

Question

I have a user defined function in SQL called getBuisnessDays it takes @startdate and @enddate and returns the number of business days between the two dates. How can I call that function within my select?

Here's what I'd like to do..

SELECT getBusinessDays(a.opendate,a.closedate) 
FROM account a
WHERE ...

Just remember that UDFs can often kill query performance depending on how the optimizer reacts to them. They often mean processing row by row rather than set-based. For your situation you might be better off using a calendar table and selecting against that when needed. — Tom H, Dec 12 '08 at 20:11

score 124 · Accepted Answer · edited Mar 19 '15 at 21:55

124

Yes, you can do almost that:

SELECT dbo.GetBusinessDays(a.opendate,a.closedate) as BusinessDays
FROM account a
WHERE...

edited Mar 19 '15 at 21:55

ΩmegaMan

29,542
12
100
122

answered Dec 12 '08 at 19:22

user17670

1,252
1
9
2

6

damn - beat me to it - you need the owner prefix – DJ. Dec 12 '08 at 19:25

score 13 · Answer 2 · edited Jan 29 '16 at 22:53

13

If it's a table-value function (returns a table set) you simply join it as a Table

this function generates one column table with all the values from passed comma-separated list

SELECT * FROM dbo.udf_generate_inlist_to_table('1,2,3,4')

edited Jan 29 '16 at 22:53

Roman Marusyk

23,328
24
73
116

answered Dec 12 '08 at 19:29

jerryhung

1,043
6
10

score 8 · Answer 3 · answered Dec 12 '08 at 19:25

8

Use a scalar-valued UDF, not a table-value one, then you can use it in a SELECT as you want.

answered Dec 12 '08 at 19:25

recursive

83,943
34
151
241

SQL User Defined Function Within Select

3 Answers3

Linked