July 6, 2009 at 6:25 am
hi,
how can we send the optional parameters to the CLR Table valued function?
please help me.
🙂
July 6, 2009 at 8:01 am
You just specify them. The problem with optional parameters in User Defined Functions is that you can't not specify them: so they are not really "optional".
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 6, 2009 at 6:10 pm
Yes, and very annoying it is too. You do 'sort of' omit them by including the DEFAULT keyword instead, but that's not really the point 🙂
Books Online
CREATE FUNCTION: 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.
July 9, 2009 at 1:47 pm
I'm really happy that C# does not give the possibility to implement optional parameters. Reason is that optional parameters are not handled by the library providing a method with optional parameters but by the calling module compiler.
In C++, if you specify a method like this in a base library
MyMethod(int foo, int bar = 0);
... and use it within another module like this:
MyMethod(1);
... The calling compiler handles the optional parameters and creates this code
MyMethod(1, 0);
... If you change the base to:
MyMethod(int foo, int bar = -1);
... without recompiling the calling module, you will get a probably wrong method call.
July 9, 2009 at 3:06 pm
Florian Reischl (7/9/2009)
I'm really happy that C# does not give the possibility to implement optional parameters.
Hey Flo,
So...you mean optional paramaters like Console.WriteLine("{0} posted {1} {2}.", "Flo", "yesterday", "afternoon"); ...? 🙂
The real annoyance from a SQLCLR point of view is the lack of overloading.
It would often be useful to be able to call a routine with a different number of parameters, or different types.
What do you think of this:
class ParameterClass
{
public string Name;
public int IDNumber;
public ParameterClass()
{
//set some default values;
//since both fields are public they can
//be overridden if necessary
this.Name = "";
this.IDNumber = 0;
}
}
class OptionalParameters
{
[STAThread]
static void Main(string[] args)
{
//instantiate a parameter class object
//and override the name field
ParameterClass c = new ParameterClass();
c.Name = "Lamont Adams";
optionalObject(c);
//show that the changed ID came back
Console.WriteLine("c.IDNumber={0}",c.IDNumber);
//call the method with only defaults
optionalObject(new ParameterClass());
//pause so we can see the output
Console.ReadLine();
}
public static void optionalObject(ParameterClass arg)
{
//because the parameters received are encapsulated
//in an object, they are all optional but have
//a valid state even if not explicitly set by the caller
Console.WriteLine("arg.Name={0}, arg.IDNumber={1}", arg.Name, arg.IDNumber);
//change one of the field values
arg.IDNumber = 10;
}
}
July 9, 2009 at 3:33 pm
Hi Paul!
Paul White (7/9/2009)
Florian Reischl (7/9/2009)
I'm really happy that C# does not give the possibility to implement optional parameters.So...you mean optional paramaters like Console.WriteLine("{0} posted {1} {2}.", "Flo", "yesterday", "afternoon"); ...? 🙂
Nope. I meant optional parameters with default values like often done in C++ or VB6 (don't know if VB.NET supports this feature any more). The "params" enables you to use an optional list of values but it does not support any default values.
The real annoyance from a SQLCLR point of view is the lack of overloading.
It would often be useful to be able to call a routine with a different number of parameters, or different types.
Confirmed! Overloading is a really nice feature in C#/C++/... The problem is TSQL doesn't support this. Since this is not changed in future versions I have no idea how they should handle this?
What do you think of this:
class ParameterClass
{
public string Name;
public int IDNumber;
public ParameterClass()
{
//set some default values;
//since both fields are public they can
//be overridden if necessary
this.Name = "";
this.IDNumber = 0;
}
}
class OptionalParameters
{
[STAThread]
static void Main(string[] args)
{
//instantiate a parameter class object
//and override the name field
ParameterClass c = new ParameterClass();
c.Name = "Lamont Adams";
optionalObject(c);
//show that the changed ID came back
Console.WriteLine("c.IDNumber={0}",c.IDNumber);
//call the method with only defaults
optionalObject(new ParameterClass());
//pause so we can see the output
Console.ReadLine();
}
public static void optionalObject(ParameterClass arg)
{
//because the parameters received are encapsulated
//in an object, they are all optional but have
//a valid state even if not explicitly set by the caller
Console.WriteLine("arg.Name={0}, arg.IDNumber={1}", arg.Name, arg.IDNumber);
//change one of the field values
arg.IDNumber = 10;
}
}
Well, maybe this will be a new feature in version after next. This sample introduces constructors for user defined table types or any other way to create a property bag 😉
Since we are apparently working on wish list for SQL Server 2011+n:
I would like to see the possibility to add procedures or functions to tables or rows like:
AddressRow.GetFullName() -- Creates a concatenated string of first name and sure name
AddressTable.Add(@first_name, @last_name) -- To get rid of "dbo.usp_Address_Add"
AddressTable.GetByCountry(@country) -- To get rid of "dbo.tvf_Address_ByCountry"
😀
July 9, 2009 at 4:00 pm
Florian Reischl (7/9/2009)
Nope. I meant optional parameters with default values like often done in C++ or VB6 (don't know if VB.NET supports this feature any more). The "params" enables you to use an optional list of values but it does not support any default values.
On a tangent: wouldn't it also be useful to be able to call routines with "params" arguments from SQL? One example that comes up again and again is the need to return the least or greatest value from a number of values:
SELECT LEAST/GREATEST(a, b, c ...[,n])
It seems odd to me that a built-in like COALESCE can accept a variable number of parameters...but nothing else?
Paul
July 9, 2009 at 4:15 pm
Paul White (7/9/2009)
Florian Reischl (7/9/2009)
Nope. I meant optional parameters with default values like often done in C++ or VB6 (don't know if VB.NET supports this feature any more). The "params" enables you to use an optional list of values but it does not support any default values.On a tangent: wouldn't it also be useful to be able to call routines with "params" arguments from SQL? One example that comes up again and again is the need to return the least or greatest value from a number of values:
SELECT LEAST/GREATEST(a, b, c ...[,n])
It seems odd to me that a built-in like COALESCE can accept a variable number of parameters...but nothing else?
Completely agreed!
I ported the IN statement to C# because it is a really nice approach to avoid "if (a = b || a = c || a = d || a = x)"
July 12, 2009 at 4:12 pm
Florian Reischl (7/9/2009)
...Nope. I meant optional parameters with default values like often done in C++ or VB6 (don't know if VB.NET supports this feature any more)...
Oh it definitely does. In fact I wouldn't consider any language civilized unless it allowed you to define optional parameters. 😉
In most architectures that I have worked in parameter defaulting is implemented by binding the defaults in the callers compiled code, because this is faster than the alternatives. But it's really only marginally faster (2 to 4 machine instructions typically), so I've never really understood why they do it this way for high-level languages VB.net and C#.
Now back in the old VB, you could specify optional parameters without having to also specify a default (in fact originally you couldn't specify defaults at all). You would just test the optional arguments to see if they were "Nothing" (VB's equivalent to Void) and substitute in your defaults if they were. It was more work in the called method, but it avoided the kind of binding problems that you mention.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 12, 2009 at 4:17 pm
Paul White (7/9/2009)
Florian Reischl (7/9/2009)
Nope. I meant optional parameters with default values like often done in C++ or VB6 (don't know if VB.NET supports this feature any more). The "params" enables you to use an optional list of values but it does not support any default values.On a tangent: wouldn't it also be useful to be able to call routines with "params" arguments from SQL? One example that comes up again and again is the need to return the least or greatest value from a number of values:
SELECT LEAST/GREATEST(a, b, c ...[,n])
It seems odd to me that a built-in like COALESCE can accept a variable number of parameters...but nothing else?
Paul
Actually, ANSI SQL covers this: some of the aggregate functions, like MIN and MAX functions are supposed to have both a "vertical" form (minimum/maximum expression value in a set of rows) and a "horizontal" form (minimum/maximum value from a lis tof expressions). So MAX(a, b, c, ...) should work.
Unfortuantely, it was never implemented in SQL Server. 🙁
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 12, 2009 at 4:22 pm
back to the original question...
Although both T-SQL and VB.net support optional parameters (in a virtually identical form), it does not appear to me that SQLCLR supports them. Though you could just put a T-SQL wrapper around the CLR proc to implement it of course.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 12, 2009 at 4:37 pm
RBarryYoung (7/12/2009)
Now back in the old VB, you could specify optional parameters without having to also specify a default (in fact originally you couldn't specify defaults at all). You would just test the optional arguments to see if they were "Nothing" (VB's equivalent to Void) and substitute in your defaults if they were. It was more work in the called method, but it avoided the kind of binding problems that you mention.
Now that's going back a bit - but didn't we have to type the optional parameters as Variant and check IsEmpty rather than checking for Nothing? I'm not sure actually.
July 12, 2009 at 5:22 pm
Paul White (7/12/2009)
RBarryYoung (7/12/2009)
Now back in the old VB, you could specify optional parameters without having to also specify a default (in fact originally you couldn't specify defaults at all). You would just test the optional arguments to see if they were "Nothing" (VB's equivalent to Void) and substitute in your defaults if they were. It was more work in the called method, but it avoided the kind of binding problems that you mention.Now that's going back a bit - but didn't we have to type the optional parameters as Variant and check IsEmpty rather than checking for Nothing? I'm not sure actually.
Yep, I think that you are right. IsEmpty actually preceeded Nothing, and I'm getting them confused because later on, I used to specify "Nothing" as the default values for optional parameters when I wanted to test & default them in the subroutine (instead having it done automatically in the caller).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 13, 2009 at 5:07 am
RBarryYoung (7/12/2009)
Paul White (7/12/2009)
RBarryYoung (7/12/2009)
Now back in the old VB, you could specify optional parameters without having to also specify a default (in fact originally you couldn't specify defaults at all). You would just test the optional arguments to see if they were "Nothing" (VB's equivalent to Void) and substitute in your defaults if they were. It was more work in the called method, but it avoided the kind of binding problems that you mention.Now that's going back a bit - but didn't we have to type the optional parameters as Variant and check IsEmpty rather than checking for Nothing? I'm not sure actually.
Yep, I think that you are right. IsEmpty actually preceeded Nothing, and I'm getting them confused because later on, I used to specify "Nothing" as the default values for optional parameters when I wanted to test & default them in the subroutine (instead having it done automatically in the caller).
VB6 is way back for me... As I remember "Nothing" was equivalent to "null" which only worked for complex data types (not Integer, String, ...). Working with optional "Variant" parameters enabled the usage of IsMissing (If IsMissing(myParam) Then...).
July 13, 2009 at 7:29 am
Florian Reischl (7/13/2009)
RBarryYoung (7/12/2009)
Paul White (7/12/2009)
RBarryYoung (7/12/2009)
Now back in the old VB, you could specify optional parameters without having to also specify a default (in fact originally you couldn't specify defaults at all). You would just test the optional arguments to see if they were "Nothing" (VB's equivalent to Void) and substitute in your defaults if they were. It was more work in the called method, but it avoided the kind of binding problems that you mention.Now that's going back a bit - but didn't we have to type the optional parameters as Variant and check IsEmpty rather than checking for Nothing? I'm not sure actually.
Yep, I think that you are right. IsEmpty actually preceeded Nothing, and I'm getting them confused because later on, I used to specify "Nothing" as the default values for optional parameters when I wanted to test & default them in the subroutine (instead having it done automatically in the caller).
VB6 is way back for me... As I remember "Nothing" was equivalent to "null" which only worked for complex data types (not Integer, String, ...). Working with optional "Variant" parameters enabled the usage of IsMissing (If IsMissing(myParam) Then...).
Heh, you're right Flo. "IsMissing(..)" Although I still do VBA projects, I haven't used that older optional parameter construct in a long, long time myself. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply