UD Table Function With Parameter in Qurey 1

[PWise]

Can Something like this be done?

I have an UDF that takes a parameter returns a table

Create function dbo.Somefunction (@Somedate DateTime)

RETURNS @SomeCalendar Table(Somedate DateTime,Dateplusx Datetime,Dateplusz Datetime)

AS

Begin
Insert into @SomeCalendar (Somedate )values (@Somedate)

Update @SomeCalendar 
Set Dateplusx =@Somedate+x
Set Dateplusy =@Somedate+y

Return

END

This Works

Select * from dbo.Somefunction(mydate)

can something like this work

Select *,dbo.Somefunction(mytable.mydatefield)
 from from Mytable

Perhaps with a join???

 

[gmmastros]

If you are using SQL2005, then you are completely out of luck. With SQL2005 or newer, I think you can accomplish this by using "cross apply".

Something like:

Select *
From   MyTable
       Cross Apply dbo.SomeFunction(MyTable.DateField) As SomeFunctionAlias

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

George you must mean
If you are using SQL2000, then you are completely out of luck

So i guess i am out of luck will have to call 4 scaler functions

 

[gmmastros]

Yes. Sorry. I meant if you are using sql 2000 you are out of luck.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom