[SQL 2005] SQLCLR UDT Comparison Methods

OK, so I'm creating a simple UDT. I want to expose a method that performs a
comparison of two objects of the type of the UDT and returns a "boolean"
value based on the result that SQL Server can use in T-SQL statements and
expressions. I understand you can't override comparison operators like = and <>. Ideally I'd like to achieve something like the following:
DECLARE @.x MyUDT
DECLARE @.y MyUDT
SELECT @.x = 'Test Value'
SELECT @.y = 'Test Value'
IF @.x.IsEqual(@.y)
PRINT 'Equal'
ELSE
PRINT 'Not Equal'
Or alternatively:
IF MyUDT::IsEqual(@.x, @.y)
PRINT 'Equal'
ELSE
PRINT 'Not Equal'
Anyone done this, or can point me to someplace with a code sample?
Thanks in advance.Almost forgot to mention. IsByteOrdered is not really an option, since this
particular type is an unordered space and <, > operators make no sense on
it. = and <> are valid operations on this type.
Thanks
"Mike C#" <xyz@.xyz.com> wrote in message
news:eq21xCaIHHA.960@.TK2MSFTNGP04.phx.gbl...
> OK, so I'm creating a simple UDT. I want to expose a method that performs
> a comparison of two objects of the type of the UDT and returns a "boolean"
> value based on the result that SQL Server can use in T-SQL statements and
> expressions. I understand you can't override comparison operators like => and <>. Ideally I'd like to achieve something like the following:
> DECLARE @.x MyUDT
> DECLARE @.y MyUDT
> SELECT @.x = 'Test Value'
> SELECT @.y = 'Test Value'
> IF @.x.IsEqual(@.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Or alternatively:
> IF MyUDT::IsEqual(@.x, @.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Anyone done this, or can point me to someplace with a code sample?
> Thanks in advance.
>|||Mike
You want to compare two values , is it possible that
SELECT @.x = 'Test value'
SELECT @.y = 'Test Value'
So , you need to PRINT 'Not Equal' , am I right?
Perhaps you can look at BINARY_CHECKSUM(*) in the BOL for comparison
SELECT a.ID, a.CheckSum
From (Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.authorsA ) a
Inner Join (
Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.authorsB ) b
On a.ID = b.ID
Where a.CheckSum != b.CheckSum
"Mike C#" <xyz@.xyz.com> wrote in message
news:eq21xCaIHHA.960@.TK2MSFTNGP04.phx.gbl...
> OK, so I'm creating a simple UDT. I want to expose a method that performs
> a comparison of two objects of the type of the UDT and returns a "boolean"
> value based on the result that SQL Server can use in T-SQL statements and
> expressions. I understand you can't override comparison operators like => and <>. Ideally I'd like to achieve something like the following:
> DECLARE @.x MyUDT
> DECLARE @.y MyUDT
> SELECT @.x = 'Test Value'
> SELECT @.y = 'Test Value'
> IF @.x.IsEqual(@.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Or alternatively:
> IF MyUDT::IsEqual(@.x, @.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Anyone done this, or can point me to someplace with a code sample?
> Thanks in advance.
>|||Uri,
I want to do it in a SQLCLR UDT by exposing a comparison method. I could
avoid the checksum functions and just use IF @.x.ToString = @.y.ToString
directly; or create a UDF to do the comparison. I'd rather expose a method
of the UDT so I can encapsulate the logic all in one spot and avoid
conversions and additional calculations/manipulations.
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23vrKJQaIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Mike
> You want to compare two values , is it possible that
> SELECT @.x = 'Test value'
> SELECT @.y = 'Test Value'
>
> So , you need to PRINT 'Not Equal' , am I right?
>
> Perhaps you can look at BINARY_CHECKSUM(*) in the BOL for comparison
>
> SELECT a.ID, a.CheckSum
> From (Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
> FROM dbo.authorsA ) a
> Inner Join (
> Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
> FROM dbo.authorsB ) b
> On a.ID = b.ID
> Where a.CheckSum != b.CheckSum
>
>
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:eq21xCaIHHA.960@.TK2MSFTNGP04.phx.gbl...
>> OK, so I'm creating a simple UDT. I want to expose a method that
>> performs a comparison of two objects of the type of the UDT and returns a
>> "boolean" value based on the result that SQL Server can use in T-SQL
>> statements and expressions. I understand you can't override comparison
>> operators like = and <>. Ideally I'd like to achieve something like the
>> following:
>> DECLARE @.x MyUDT
>> DECLARE @.y MyUDT
>> SELECT @.x = 'Test Value'
>> SELECT @.y = 'Test Value'
>> IF @.x.IsEqual(@.y)
>> PRINT 'Equal'
>> ELSE
>> PRINT 'Not Equal'
>> Or alternatively:
>> IF MyUDT::IsEqual(@.x, @.y)
>> PRINT 'Equal'
>> ELSE
>> PRINT 'Not Equal'
>> Anyone done this, or can point me to someplace with a code sample?
>> Thanks in advance.
>|||Mike C# (xyz@.xyz.com) writes:
> OK, so I'm creating a simple UDT. I want to expose a method that
> performs a comparison of two objects of the type of the UDT and returns
> a "boolean" value based on the result that SQL Server can use in T-SQL
> statements and expressions. I understand you can't override comparison
> operators like = and <>. Ideally I'd like to achieve something like the
> following:
> DECLARE @.x MyUDT
> DECLARE @.y MyUDT
> SELECT @.x = 'Test Value'
> SELECT @.y = 'Test Value'
> IF @.x.IsEqual(@.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
Apart from that the syntax most certainly would have to be
IF @.x.IsEqual(@.y) = 1
what is really your problem?
In the Point sample in Books Online, there is a method Distance where the
input is another point. See
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1e5b43b3-4971-45ee-a591-3f535e2ac722.htm
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989CD80E49468Yazorman@.127.0.0.1...
> Mike C# (xyz@.xyz.com) writes:
>> OK, so I'm creating a simple UDT. I want to expose a method that
>> performs a comparison of two objects of the type of the UDT and returns
>> a "boolean" value based on the result that SQL Server can use in T-SQL
>> statements and expressions. I understand you can't override comparison
>> operators like = and <>. Ideally I'd like to achieve something like the
>> following:
>> DECLARE @.x MyUDT
>> DECLARE @.y MyUDT
>> SELECT @.x = 'Test Value'
>> SELECT @.y = 'Test Value'
>> IF @.x.IsEqual(@.y)
>> PRINT 'Equal'
>> ELSE
>> PRINT 'Not Equal'
> Apart from that the syntax most certainly would have to be
> IF @.x.IsEqual(@.y) = 1
> what is really your problem?
My problem really is that IF @.x.IsEqual(@.y) = 1 is not a function that
returns or compares a boolean value. My problem is that there appears to be
no way to use a SqlBoolean return value from a method in an IF statement. I
want to know if that is correct or not. Is SqlBoolean of any use whatsoever
in the context of a UDT?
> In the Point sample in Books Online, there is a method Distance where the
> input is another point. See
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1e5b43b3-4971-45ee-a591-3f535e2ac722.htm
Unfortunately I'm not interested in returning a numeric result of a
calculation on two UDTs. I'm interested in comparing two UDT's that are not
byte-ordered for equality. I notice that the MS Point sample is
byte-ordered; does this indicate that a point (X, Y+1000000) is actually
"less than" the point (X+1, Y-100000)? I noticed in my UDT that when I
remove byte-ordering (since byte-ordering is inapplicable in my case), I
cannot use the T-SQL "=" comparison operator on two variables of the UDT's
type. The UDT is Format.Native.
If you decide for some reason that "less than" and "greater than" have no
meaning in regards to your UDT, and remove the byte-ordering, what's the
best way to test them for equality/inequality? Is returning arbitrary "flag
values" from methods and comparing those flag values for equality to
constants the only way? Is there no way to return a boolean value (aka,
SqlBoolean) and use it in an IF statement?|||Mike C# (xyz@.xyz.com) writes:
> My problem really is that IF @.x.IsEqual(@.y) = 1 is not a function that
> returns or compares a boolean value. My problem is that there appears
> to be no way to use a SqlBoolean return value from a method in an IF
> statement.
There is:
IF @.x.IsEqual(@.y) = 1
Of if you prefer:
IF @.x.IsEqual(@.y) = 'true'
SqlBoolean is the .Net correspondence to the T-SQL data type bit, and this
how you use bit in T-SQL. T-SQL is funny, because while it has boolean
expressions, it does not have a boolean data type.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Mike,
> Unfortunately I'm not interested in returning a numeric result of a
> calculation on two UDTs. I'm interested in comparing two UDT's that are
> not byte-ordered for equality. I notice that the MS Point sample is
> byte-ordered; does this indicate that a point (X, Y+1000000) is actually
> "less than" the point (X+1, Y-100000)? I noticed in my UDT that when I
> remove byte-ordering (since byte-ordering is inapplicable in my case), I
> cannot use the T-SQL "=" comparison operator on two variables of the UDT's
> type. The UDT is Format.Native.
> If you decide for some reason that "less than" and "greater than" have no
> meaning in regards to your UDT, and remove the byte-ordering, what's the
> best way to test them for equality/inequality? Is returning arbitrary
> "flag values" from methods and comparing those flag values for equality to
> constants the only way? Is there no way to return a boolean value (aka,
> SqlBoolean) and use it in an IF statement?
How about still using byte ordering, but implemented in your own way? You
could implement IBinarySerialize interface in your UDT and then in the Write
method write additional 8 bytes in the beginning of the serialized value.
You can calculate the value in this 8 bytes anyway you like, so you can
define also what is greater than and what is lower thaan, not just equal.
When you deserialize a value from this UDT, i.e. in the Read method, just
skip these first 8 bytes.
--
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uCbKXtrIHHA.5000@.TK2MSFTNGP03.phx.gbl...
> How about still using byte ordering, but implemented in your own way? You
> could implement IBinarySerialize interface in your UDT and then in the
> Write method write additional 8 bytes in the beginning of the serialized
> value. You can calculate the value in this 8 bytes anyway you like, so you
> can define also what is greater than and what is lower thaan, not just
> equal. When you deserialize a value from this UDT, i.e. in the Read
> method, just skip these first 8 bytes.
Dejan, in some instances greater than and less than are meaningless. This
is the case here. Equality and inequality, however, have meaning. I would
like to get the functionality of equality and inequality without less than,
greater than, etc.
Thanks.|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989D60A6568D6Yazorman@.127.0.0.1...
> Mike C# (xyz@.xyz.com) writes:
>> My problem really is that IF @.x.IsEqual(@.y) = 1 is not a function that
>> returns or compares a boolean value. My problem is that there appears
>> to be no way to use a SqlBoolean return value from a method in an IF
>> statement.
> There is:
> IF @.x.IsEqual(@.y) = 1
> Of if you prefer:
> IF @.x.IsEqual(@.y) = 'true'
> SqlBoolean is the .Net correspondence to the T-SQL data type bit, and this
> how you use bit in T-SQL. T-SQL is funny, because while it has boolean
> expressions, it does not have a boolean data type.
You know what it boils down to? They need to allow you to override T-SQL
operators for UDT's, plain and simple. For all that, I may as well just
write IF CAST(@.x AS VARBINARY) = CAST(@.y AS VARBINARY). I was hoping taht
(as a next best option), there would be a way to treat a .NET method as a
boolean expression, but that appears to not be the case. Looks like it's
just a matter of returning a value and arbitrarily assigning meaning to it.
Thanks.|||Mike C# (xyz@.xyz.com) writes:
> You know what it boils down to? They need to allow you to override T-SQL
> operators for UDT's, plain and simple.
Because you would wear out your fingers if you have to type "= 1"? C'mon,
I know have C# in your alias, but you are not in Kansas anymore. When in
Rome, do as the Romans.
But if you feel strongly about it, http://connect.microsoft.com is where
you can submit suggestions for enhancements.
> For all that, I may as well just write IF CAST(@.x AS VARBINARY) => CAST(@.y AS VARBINARY).
Yes, if your equality check is that simple. The above would not work for
some of T-SQL data types in some collations.
> I was hoping taht (as a next best option), there would be a way to treat
> a .NET method as a boolean expression, but that appears to not be the
> case. Looks like it's just a matter of returning a value and
> arbitrarily assigning meaning to it.
You would have precisely the same issue with a UDF that returns 0 or 1.
Or for that matter a bit column in a table which represents something
that is off or on.
Why would you need a special syntax just because it's a .Net UDT?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989DB977C7FECYazorman@.127.0.0.1...
> Mike C# (xyz@.xyz.com) writes:
>> You know what it boils down to? They need to allow you to override T-SQL
>> operators for UDT's, plain and simple.
> Because you would wear out your fingers if you have to type "= 1"? C'mon,
> I know have C# in your alias, but you are not in Kansas anymore. When in
> Rome, do as the Romans.
Simple consistency. You use "=1" as the status for a match, Harry uses
"=0", Linda uses "='True'", Gorky from B.F.E. Sweden uses "='Y'". BTW,
haven't been to Kansas myself and just in case you didn't get the memo, the
Romans did as the Romans do while in Rome, and the Roman Empire went bye-bye
for precisely that reason.
> But if you feel strongly about it, http://connect.microsoft.com is where
> you can submit suggestions for enhancements.
Fell strongly? Not hardly. I was just asking a question - "Is it
possible?" Apparently not. That's the end of that as far as I'm concerned.
>> For all that, I may as well just write IF CAST(@.x AS VARBINARY) =>> CAST(@.y AS VARBINARY).
> Yes, if your equality check is that simple. The above would not work for
> some of T-SQL data types in some collations.
Just wondering what "some of T-SQL data types in some collations" has to do
with comparing the binary representation of a SQLCLR UDT?
>> I was hoping taht (as a next best option), there would be a way to treat
>> a .NET method as a boolean expression, but that appears to not be the
>> case. Looks like it's just a matter of returning a value and
>> arbitrarily assigning meaning to it.
> You would have precisely the same issue with a UDF that returns 0 or 1.
> Or for that matter a bit column in a table which represents something
> that is off or on.
Consistency.
> Why would you need a special syntax just because it's a .Net UDT?
How's this for special syntax?
DECLARE @.m1 MyUDT
DECLARE @.m2 MyUDT
IF @.m1 = @.m2
PRINT 'Equal'
ELSE
PRINT 'Not Equal'
Unforunately (as I said previously) it doesn't seem to work when the Byte
Ordering attribute is set to false. It would appear that the Romans feel if
two items can't be sorted, then neither can they be equal.|||Mike C# (xyz@.xyz.com) writes:
> Simple consistency.
Why then ask for a special feature for .Net UDT methods?
> You use "=1" as the status for a match, Harry uses
> "=0", Linda uses "='True'", Gorky from B.F.E. Sweden uses "='Y'".
Using bit to represent boolean values with 1 for truth is quite standard
in the T-SQL world. So why should .Net UDT methods be special?
>> For all that, I may as well just write IF CAST(@.x AS VARBINARY) =>> CAST(@.y AS VARBINARY).
>> Yes, if your equality check is that simple. The above would not work for
>> some of T-SQL data types in some collations.
> Just wondering what "some of T-SQL data types in some collations" has to
> do with comparing the binary representation of a SQLCLR UDT?
As an example that equality is not always possible by byte-comparing,
even if it might be in your case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989E6F414054Yazorman@.127.0.0.1...
> Mike C# (xyz@.xyz.com) writes:
>> Simple consistency.
> Why then ask for a special feature for .Net UDT methods?
>> You use "=1" as the status for a match, Harry uses
>> "=0", Linda uses "='True'", Gorky from B.F.E. Sweden uses "='Y'".
> Using bit to represent boolean values with 1 for truth is quite standard
> in the T-SQL world. So why should .Net UDT methods be special?
It is also common in the T-SQL world to use 'Y', 'T', and 'True' for
Yes/True. Yet none of these (1 inclusive) is the same thing as TRUE, or you
could logically expect something like this to work:
IF 1
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
However the result of 1 = 1 *is* TRUE, so you can expect this to work:
IF 1 = 1
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
Or even this:
DECLARE @.i INT
DECLARE @.j INT
SELECT @.i = 1
SELECT @.j = 1
IF @.i = @.j
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
*Yet this doesn't*:
DECLARE @.i MyUDT
DECLARE @.j MyUDT
SELECT @.i = '(1,2,3)'
SELECT @.j = '(1,2,3)'
IF @.i = @.j
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
It appears that .NET UDT equality operators are "special" for non-byte
ordered types. As I stated, the conclusion seems to be if two items can't
be sorted they can't be compared for equality.
>> Just wondering what "some of T-SQL data types in some collations" has to
>> do with comparing the binary representation of a SQLCLR UDT?
> As an example that equality is not always possible by byte-comparing,
> even if it might be in your case.
I don't see how collation even comes into the picture here, but the fact
that equality is not always possible by byte-comparing is another reason it
would be just *dandy* to have the capability of overloading the T-SQL
equality operator for your UDT.
Bottom line is this: I asked if it was possible to do "x". You said it is
*not* possible to do "x".
That's great. Case closed. Problem solved. Over and out.