July 20, 2012 at 2:26 am
If i was to pass a new Parameter into a Stored Procedure, I would give it a default value. Maybe that value would be a NULL.
This would mean that i would not need to update all areas that called that Stored Procedure because it wouldn't be demanding a value be passed in.
For a Function though, this doesn't seem to be the case. If i add a new Parameter to that and give it a default of NULL, it instantly stops all calls to that Function from working and claiming 'An insufficient number of arguments were supplied for the procedure or function dbo.[function_name]''.
In this scenario I am forced to add the default key word into the relevant position (or just the relevant default value - in this case, NULL.):
e.g
select col1 from dbo.udf_myFunction(@param1,@param2,default)
i'm a missing the point here? am i just not doing something right, because it seems a bit weird.:crazy:
July 20, 2012 at 3:22 am
This is what I got from Books Online
When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called to retrieve the default value. This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value. However, the DEFAULT keyword is not required when invoking a scalar function by using the EXECUTE statement.
Check the link mentioned below for more details
http://msdn.microsoft.com/en-us/library/ms186755.aspx
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 20, 2012 at 5:26 am
Interesting, I didn't know you could call functions using EXECUTE!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply