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
– Tom H, Commented Dec 12, 2008 at 20:11

ΩmegaMan · Accepted Answer · 2015-03-19 21:55:57Z

126

Yes, you can do almost that:

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

edited Mar 19, 2015 at 21:55

ΩmegaMan

32.2k13 gold badges110 silver badges138 bronze badges

answered Dec 12, 2008 at 19:22

user17670

1,2721 gold badge9 silver badges2 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DJ. Over a year ago

damn - beat me to it - you need the owner prefix

Roman Marusyk · Accepted Answer · 2016-01-29 22:53:45Z

15

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, 2016 at 22:53

Roman Marusyk

24.8k27 gold badges83 silver badges127 bronze badges

answered Dec 12, 2008 at 19:29

jerryhung

1,1216 silver badges11 bronze badges

Comments

recursive · Accepted Answer · 2008-12-12 19:25:35Z

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, 2008 at 19:25

recursive

86.5k36 gold badges155 silver badges246 bronze badges

Collectives™ on Stack Overflow

SQL User Defined Function Within Select

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related