April 27, 2009 at 2:38 pm
I have a feeling the answer is No but darned if i can find within BOL anything explcitly stating if this is allowed or not.
If I have a Table-Valued function can I execute it, specifying the UDF's Parameter Names like this:
SELECT MyFunc.*
FROM dbo.udf_My_UDF(@sParam1='SomeValue',@sParam3='SomeOtherValue') MyFunc
I have more then 1 custom UDF with default values assigned to 1 or more Parameters and it sure would be nice to be able to call these UDF's and explictly specify the value(s) for the various Parameters but I can't seem to make this work no matter how I do it.
If anyone knows where in BOL it explcitly mentions this with Table-Valued functions I'd love to know where you see this info.
Thanks
Kindest Regards,
Just say No to Facebook!April 27, 2009 at 2:49 pm
I can't find it in BOL, but I think you're SOL. 😉
Best you can do is this, and it still means keeping the positions of the parameters
SELECT MyFunc.*
FROM dbo.udf_My_UDF('SomeValue',DEFAULT,'SomeOtherValue') MyFunc
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 27, 2009 at 3:42 pm
Bob Hovious (4/27/2009)
I can't find it in BOL, but I think you're SOL. 😉Best you can do is this, and it still means keeping the positions of the parameters
SELECT MyFunc.*
FROM dbo.udf_My_UDF('SomeValue',DEFAULT,'SomeOtherValue') MyFunc
I think you're right to. I did find in BOL where one might could say (with a lot of give) that BOL implies in an out of the way wording that this is the case. In BOL where it details how to Execute a UDF, for the details on a Scalar Valued UDF it says you don;t have to provide values fro any paramters set with DEFAULTS when you call the UDF and specify the Paramater names. That's a very, very week implict reference on whether you can do the same for Tale-Valued UDF's but I can find nothing else on this.
I just can't believe many programmera haven't posted and complained about this since this is a give in procedural programming and with how many non-DBA types are now performing the role of the DBA.
Thanks
Kindest Regards,
Just say No to Facebook!April 27, 2009 at 8:25 pm
Send me the link you have for that in BOL, please.
I took a quick look and found that you can use this format when you EXEC a UDF.
Don't know if this helps.
--
declare @results datetime
exec @results = dbo.ufAddBizDays @bizdays=6, @startDate = '4/1/2009'
select @results
--
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply