[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...
>|||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-3f
535e2ac722.htm
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello Mike,
I would also suggest not to implement this as a UDT method but a seperate
function. The reason is that once you have the udt used changing the assembl
y
is very difficult. If you have a bug in your comparision method or you want
to change it slightly this is very difficult. Having it as a seperate assemb
ly
is much easier to maintain.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
[vbcol=seagreen]
> 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...
>|||"Simon Sabin" <SimonSabin@.noemail.noemail> wrote in message
news:62959f1a378838c8f04b36aae4dc@.msnews
.microsoft.com...
> Hello Mike,
> I would also suggest not to implement this as a UDT method but a seperate
> function. The reason is that once you have the udt used changing the
> assembly is very difficult. If you have a bug in your comparision method
> or you want to change it slightly this is very difficult. Having it as a
> seperate assembly is much easier to maintain.
...Not what I'm interested in doing at all. But thanks.|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989CD80E49468Yazorman@.127.0.0.1...
> Mike C# (xyz@.xyz.com) writes:
> 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-3f535e2a
c722.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?|||Simon Sabin (SimonSabin@.noemail.noemail) writes:
> I would also suggest not to implement this as a UDT method but a
> seperate function. The reason is that once you have the udt used
> changing the assembly is very difficult. If you have a bug in your
> comparision method or you want to change it slightly this is very
> difficult. Having it as a seperate assembly is much easier to maintain.
It was a while since I played with it, but as long as you only change the
implementation of methods and properties, ALTER ASSEMBLY works great. But
if you change the physical storage of the type, then it indeed it's painful.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx