UDF that take a UDT as a parameter (CLR)

7 Sep 2006 7:10 PM

Andy in S. Jersey

I have a feeling that I can't do this, but thought someone might have a way
to work around this.

I have a UDT type, called interval, that I want to be a parameter to a UDF.

Let's say interval 1, i1 is [2:4] and interval 2, i2 is [3:5].

I would like to do something like

i1 intersect i2 = [3:4]

So, it would look something like:

intersect (i1, i2)

and return me an interval type.

I have read in the VS documentation:

..NET Framework SVFs are implemented as methods on a class in a .NET
Framework assembly. The input parameters and the type returned from a SVF can
be any of the scalar data types supported by SQL Server, except rowversion,
text, ntext, image, timestamp, table, or cursor. SVFs must ensure a match
between the SQL Server data type and the return data type of the
implementation method.

So, I don't have much hope, but I thought I might see what someone has to
say.

7 Sep 2006 10:05 PM

Kent Tegels

Hello Andy in S. Jersey Andy in S.,

> I have a feeling that I can't do this, but thought someone might have
> a way to work around this.

This seems to work. Consider:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Text.RegularExpressions;
[Serializable]
[SqlUserDefinedType(Format.UserDefined,IsByteOrdered=true,IsFixedLength=false,MaxByteSize=8000)]
public class IntervalUDT : INullable, IBinarySerialize
{
    static readonly Regex PARSER = new Regex(@"(\d*)\:(\d*)");
    int? Start = null;
    int? End = null;
    public override string ToString() {
        return string.Format("{0}:{1}",Start,End);
    }
    public bool IsNull {
        get {
           return (Start == null? true : false) ;
        }
    }
    public static IntervalUDT Null {
        get {
            IntervalUDT h = new IntervalUDT();
            return h;
        }
    }
    public static IntervalUDT Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        IntervalUDT u = new IntervalUDT();
        string[] v = PARSER.Split(s.Value);
        u.Start = int.Parse(v[1]);
        u.End = int.Parse(v[2]);
        return u;
    }
    public void Read(BinaryReader r) {
        this.Start = r.ReadInt32();
        this.End = r.ReadInt32();
    }
    public void Write(BinaryWriter w) {
        w.Write((int)this.Start);
        w.Write((int)this.End);
    }
    public SqlInt32 GetStart() {
        if(Start == null)
            return SqlInt32.Null;
        else
            return new SqlInt32(this.Start.Value);
    }
    public SqlInt32 GetEnd() {
        if(End == null)
            return SqlInt32.Null;
        else
            return new SqlInt32(this.End.Value);
    }
}

and

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Collections;
public partial class IntervalFunctions
{
    [SqlFunction(FillRowMethodName="GetRows",TableDefinition="v int")]
    public static IEnumerable IntervalIntersect(IntervalUDT A,IntervalUDT B)
    {
        List<int?> alist = new List<int?>();
        List<int?> ilist = new List<int?>();
        for(int i = A.GetStart().Value;i <= A.GetEnd();i++)
            alist.Add(i);
        for(int i = B.GetStart().Value;i <= B.GetEnd();i++)
            if(alist.Contains(i))
                ilist.Add(i);
        return(ilist);
    }
    public static void GetRows(object o, out SqlInt32 v) {
        v = (o == null ? SqlInt32.Null : new SqlInt32((int)(o)) );
    }
};

This query seems to work:

declare @u1 IntervalUDT
declare @u2 IntervalUDT
set @u1 = '2:4'
set @u2 = '3:5'
select * from dbo.IntervalIntersect(@u1,@u2)

Cheers,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/

9 Sep 2006 8:07 PM

Andy in S. Jersey

Thanks for you help Kent.

This code looks pretty good, but let's say the test.sql is

declare @u1 IntervalUDT
declare @u2 IntervalUDT
set @u1 = '2:5'
set @u2 = '3:7'
select * from dbo.IntervalIntersect(@u1,@u2)

This would return

v
-----------
3
4
5

I would want only

v
-----------
3
5

and then convert that into my interval type again for storage in a result
table in the format

3:5

Show quoteHide quote

"Kent Tegels" wrote:

> Hello Andy in S. Jersey Andy in S.,
>
> > I have a feeling that I can't do this, but thought someone might have
> > a way to work around this.
>
> This seems to work. Consider:
>
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
> using System.IO;
> using System.Text.RegularExpressions;
> [Serializable]
> [SqlUserDefinedType(Format.UserDefined,IsByteOrdered=true,IsFixedLength=false,MaxByteSize=8000)]
> public class IntervalUDT : INullable, IBinarySerialize
> {
>     static readonly Regex PARSER = new Regex(@"(\d*)\:(\d*)");
>     int? Start = null;
>     int? End = null;
>     public override string ToString() {
>         return string.Format("{0}:{1}",Start,End);
>     }
>     public bool IsNull {
>         get {
>            return (Start == null? true : false) ;
>         }
>     }
>     public static IntervalUDT Null {
>         get {
>             IntervalUDT h = new IntervalUDT();
>             return h;
>         }
>     }
>     public static IntervalUDT Parse(SqlString s)
>     {
>         if (s.IsNull)
>             return Null;
>         IntervalUDT u = new IntervalUDT();
>         string[] v = PARSER.Split(s.Value);
>         u.Start = int.Parse(v[1]);
>         u.End = int.Parse(v[2]);
>         return u;
>     }
>     public void Read(BinaryReader r) {
>         this.Start = r.ReadInt32();
>         this.End = r.ReadInt32();
>     }
>     public void Write(BinaryWriter w) {
>         w.Write((int)this.Start);
>         w.Write((int)this.End);
>     }
>     public SqlInt32 GetStart() {
>         if(Start == null)
>             return SqlInt32.Null;
>         else
>             return new SqlInt32(this.Start.Value);
>     }
>     public SqlInt32 GetEnd() {
>         if(End == null)
>             return SqlInt32.Null;
>         else
>             return new SqlInt32(this.End.Value);
>     }
> }
>
> and
>
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
> using System.Collections.Generic;
> using System.Collections;
> public partial class IntervalFunctions
> {
>     [SqlFunction(FillRowMethodName="GetRows",TableDefinition="v int")]
>     public static IEnumerable IntervalIntersect(IntervalUDT A,IntervalUDT B)
>     {
>         List<int?> alist = new List<int?>();
>         List<int?> ilist = new List<int?>();
>         for(int i = A.GetStart().Value;i <= A.GetEnd();i++)
>             alist.Add(i);
>         for(int i = B.GetStart().Value;i <= B.GetEnd();i++)
>             if(alist.Contains(i))
>                 ilist.Add(i);
>         return(ilist);
>     }
>     public static void GetRows(object o, out SqlInt32 v) {
>         v = (o == null ? SqlInt32.Null : new SqlInt32((int)(o)) );
>     }
> };
>
> This query seems to work:
>
> declare @u1 IntervalUDT
> declare @u2 IntervalUDT
> set @u1 = '2:4'
> set @u2 = '3:5'
> select * from dbo.IntervalIntersect(@u1,@u2)
>
> Cheers,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>
>
>

9 Sep 2006 10:29 PM

Kent Tegels

Hello Andy in S. Jersey,

Well, you can either return an instance of the type OR a table of variables.
I'll leave the table min and max values as an excercise for you, but here's
modified code to give you back a type instance.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Collections;
public partial class IntervalFunctions {
    [SqlFunction()]
    public static IntervalUDT IntervalIntersect2(IntervalUDT A,IntervalUDT
B) {
        IntervalUDT c = new IntervalUDT();
        c.Start = Math.Max(A.Start.Value,B.Start.Value);
        c.End = Math.Min(A.End.Value,B.End.Value);
        return c;
    }
    [SqlFunction(FillRowMethodName="GetRows",TableDefinition="v int")]
    public static IEnumerable IntervalIntersect(IntervalUDT A,IntervalUDT
B) {
        List<int?> alist = new List<int?>();
        List<int?> ilist = new List<int?>();
        for(int i = A.Start.Value;i <= A.End.Value;i++)
            alist.Add(i);
        for(int i = B.Start.Value;i <= B.End.Value;i++)
            if(alist.Contains(i))
                ilist.Add(i);
        return(ilist);
    }
    public static void GetRows(object o, out SqlInt32 v) {
        v = (o == null ? SqlInt32.Null : new SqlInt32((int)(o)) );
    }
};

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Text.RegularExpressions;
[Serializable]
[SqlUserDefinedType(Format.UserDefined,IsByteOrdered=true,IsFixedLength=false,MaxByteSize=8000)]
public class IntervalUDT : INullable, IBinarySerialize {
    static readonly Regex PARSER = new Regex(@"(\d*)\:(\d*)");
    int? _Start = null;
    int? _End = null;
    public override string ToString() {
        return string.Format("{0}:{1}",_Start,_End);
    }
    public bool IsNull {
        get {
           return (_Start == null? true : false) ;
        }
    }
    public static IntervalUDT Null {
        get {
            IntervalUDT h = new IntervalUDT();
            return h;
        }
    }
    public static IntervalUDT Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        IntervalUDT u = new IntervalUDT();
        string[] v = PARSER.Split(s.Value);
        u._Start = int.Parse(v[1]);
        u._End = int.Parse(v[2]);
        return u;
    }
    public void Read(BinaryReader r) {
        this._Start = r.ReadInt32();
        this._End = r.ReadInt32();
    }
    public void Write(BinaryWriter w) {
        w.Write((int)this._Start);
        w.Write((int)this._End);
    }
    public SqlInt32 Start {
        get {   if(this._Start == null)
                    return SqlInt32.Null;
                else
                    return new SqlInt32(this._Start.Value); }
        set { this._Start = value.Value; }
    }
    public SqlInt32 End {
        get {   if(this._End == null)
                    return SqlInt32.Null;
                else
                    return new SqlInt32(this._End.Value); }
        set { this._End = value.Value; }
    }
}
Test with:

declare @u1 IntervalUDT
declare @u2 IntervalUDT
set @u1 = '2:5'
set @u2 = '3:7'
/* select * from dbo.IntervalIntersect(@u1,@u2) */
select dbo.IntervalIntersect2(@u1,@u2).ToString()

Show quoteHide quote

> Thanks for you help Kent.
>
> This code looks pretty good, but let's say the test.sql is
>
> declare @u1 IntervalUDT
> declare @u2 IntervalUDT
> set @u1 = '2:5'
> set @u2 = '3:7'
> select * from dbo.IntervalIntersect(@u1,@u2)
> This would return
>
> v ----------- 3 4 5
>
> I would want only
>
> v ----------- 3 5
>
> and then convert that into my interval type again for storage in a
> result table in the format
>
> 3:5
>
> "Kent Tegels" wrote:
>
>> Hello Andy in S. Jersey Andy in S.,
>>
>>> I have a feeling that I can't do this, but thought someone might
>>> have a way to work around this.
>>>
>> This seems to work. Consider:
>>
>> using System;
>> using System.Data;
>> using System.Data.SqlClient;
>> using System.Data.SqlTypes;
>> using Microsoft.SqlServer.Server;
>> using System.IO;
>> using System.Text.RegularExpressions;
>> [Serializable]
>> [SqlUserDefinedType(Format.UserDefined,IsByteOrdered=true,IsFixedLeng
>> th=false,MaxByteSize=8000)]
>> public class IntervalUDT : INullable, IBinarySerialize
>> {
>> static readonly Regex PARSER = new Regex(@"(\d*)\:(\d*)");
>> int? Start = null;
>> int? End = null;
>> public override string ToString() {
>> return string.Format("{0}:{1}",Start,End);
>> }
>> public bool IsNull {
>> get {
>> return (Start == null? true : false) ;
>> }
>> }
>> public static IntervalUDT Null {
>> get {
>> IntervalUDT h = new IntervalUDT();
>> return h;
>> }
>> }
>> public static IntervalUDT Parse(SqlString s)
>> {
>> if (s.IsNull)
>> return Null;
>> IntervalUDT u = new IntervalUDT();
>> string[] v = PARSER.Split(s.Value);
>> u.Start = int.Parse(v[1]);
>> u.End = int.Parse(v[2]);
>> return u;
>> }
>> public void Read(BinaryReader r) {
>> this.Start = r.ReadInt32();
>> this.End = r.ReadInt32();
>> }
>> public void Write(BinaryWriter w) {
>> w.Write((int)this.Start);
>> w.Write((int)this.End);
>> }
>> public SqlInt32 GetStart() {
>> if(Start == null)
>> return SqlInt32.Null;
>> else
>> return new SqlInt32(this.Start.Value);
>> }
>> public SqlInt32 GetEnd() {
>> if(End == null)
>> return SqlInt32.Null;
>> else
>> return new SqlInt32(this.End.Value);
>> }
>> }
>> and
>>
>> using System;
>> using System.Data;
>> using System.Data.SqlClient;
>> using System.Data.SqlTypes;
>> using Microsoft.SqlServer.Server;
>> using System.Collections.Generic;
>> using System.Collections;
>> public partial class IntervalFunctions
>> {
>> [SqlFunction(FillRowMethodName="GetRows",TableDefinition="v int")]
>> public static IEnumerable IntervalIntersect(IntervalUDT A,IntervalUDT
>> B)
>> {
>> List<int?> alist = new List<int?>();
>> List<int?> ilist = new List<int?>();
>> for(int i = A.GetStart().Value;i <= A.GetEnd();i++)
>> alist.Add(i);
>> for(int i = B.GetStart().Value;i <= B.GetEnd();i++)
>> if(alist.Contains(i))
>> ilist.Add(i);
>> return(ilist);
>> }
>> public static void GetRows(object o, out SqlInt32 v) {
>> v = (o == null ? SqlInt32.Null : new SqlInt32((int)(o)) );
>> }
>> };
>> This query seems to work:
>>
>> declare @u1 IntervalUDT
>> declare @u2 IntervalUDT
>> set @u1 = '2:4'
>> set @u2 = '3:5'
>> select * from dbo.IntervalIntersect(@u1,@u2)
>> Cheers,
>> Kent Tegels, DevelopMentor
>> http://staff.develop.com/ktegels/
---
Thanks,

Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/

13 Sep 2006 12:32 AM

Andy in S. Jersey

Thanks again Kent.

I am a pretty good T-SQL programmer, but C# is not my best language, please
bear with me.

The only thing I added was I changed the IntervalIntersect2 function to
accomodate the fact that the two intervals may not intersect:

            public static IntervalUDT IntervalIntersect2(IntervalUDT A,
IntervalUDT B)
    {
         IntervalUDT c = new IntervalUDT();
        if ((A.End.Value < B.Start.Value) || (B.End.Value < A.Start.Value))
        {
            return null;
        }
        else
        {
            c.Start = Math.Max(A.Start.Value, B.Start.Value);
            c.End = Math.Min(A.End.Value, B.End.Value);
            return c;
        }

    }

I think this is the best way ie to check for intersection before you do the
math funcs.

So, now, if you have intervals that don't intersect

declare @u1 IntervalUDT
declare @u2 IntervalUDT
set @u1 = '2:5'
set @u2 = '6:8'
select dbo.IntervalIntersect2(@u1,@u2).ToString()

You will get null back.

Show quoteHide quote

"Kent Tegels" wrote:

> Hello Andy in S. Jersey,
>
> Well, you can either return an instance of the type OR a table of variables.
> I'll leave the table min and max values as an excercise for you, but here's
> modified code to give you back a type instance.
>
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
> using System.Collections.Generic;
> using System.Collections;
> public partial class IntervalFunctions {
>     [SqlFunction()]
>     public static IntervalUDT IntervalIntersect2(IntervalUDT A,IntervalUDT
> B) {
>         IntervalUDT c = new IntervalUDT();
>         c.Start = Math.Max(A.Start.Value,B.Start.Value);
>         c.End = Math.Min(A.End.Value,B.End.Value);
>         return c;
>     }
>     [SqlFunction(FillRowMethodName="GetRows",TableDefinition="v int")]
>     public static IEnumerable IntervalIntersect(IntervalUDT A,IntervalUDT
> B) {
>         List<int?> alist = new List<int?>();
>         List<int?> ilist = new List<int?>();
>         for(int i = A.Start.Value;i <= A.End.Value;i++)
>             alist.Add(i);
>         for(int i = B.Start.Value;i <= B.End.Value;i++)
>             if(alist.Contains(i))
>                 ilist.Add(i);
>         return(ilist);
>     }
>     public static void GetRows(object o, out SqlInt32 v) {
>         v = (o == null ? SqlInt32.Null : new SqlInt32((int)(o)) );
>     }
> };
>
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
> using System.IO;
> using System.Text.RegularExpressions;
> [Serializable]
> [SqlUserDefinedType(Format.UserDefined,IsByteOrdered=true,IsFixedLength=false,MaxByteSize=8000)]
> public class IntervalUDT : INullable, IBinarySerialize {
>     static readonly Regex PARSER = new Regex(@"(\d*)\:(\d*)");
>     int? _Start = null;
>     int? _End = null;
>     public override string ToString() {
>         return string.Format("{0}:{1}",_Start,_End);
>     }
>     public bool IsNull {
>         get {
>            return (_Start == null? true : false) ;
>         }
>     }
>     public static IntervalUDT Null {
>         get {
>             IntervalUDT h = new IntervalUDT();
>             return h;
>         }
>     }
>     public static IntervalUDT Parse(SqlString s)
>     {
>         if (s.IsNull)
>             return Null;
>         IntervalUDT u = new IntervalUDT();
>         string[] v = PARSER.Split(s.Value);
>         u._Start = int.Parse(v[1]);
>         u._End = int.Parse(v[2]);
>         return u;
>     }
>     public void Read(BinaryReader r) {
>         this._Start = r.ReadInt32();
>         this._End = r.ReadInt32();
>     }
>     public void Write(BinaryWriter w) {
>         w.Write((int)this._Start);
>         w.Write((int)this._End);
>     }
>     public SqlInt32 Start {
>         get {   if(this._Start == null)
>                     return SqlInt32.Null;
>                 else
>                     return new SqlInt32(this._Start.Value); }
>         set { this._Start = value.Value; }
>     }
>     public SqlInt32 End {
>         get {   if(this._End == null)
>                     return SqlInt32.Null;
>                 else
>                     return new SqlInt32(this._End.Value); }
>         set { this._End = value.Value; }
>     }
> }
> Test with:
>
> declare @u1 IntervalUDT
> declare @u2 IntervalUDT
> set @u1 = '2:5'
> set @u2 = '3:7'
> /* select * from dbo.IntervalIntersect(@u1,@u2) */
> select dbo.IntervalIntersect2(@u1,@u2).ToString()
>
>
>
>
> > Thanks for you help Kent.
> >
> > This code looks pretty good, but let's say the test.sql is
> >
> > declare @u1 IntervalUDT
> > declare @u2 IntervalUDT
> > set @u1 = '2:5'
> > set @u2 = '3:7'
> > select * from dbo.IntervalIntersect(@u1,@u2)
> > This would return
> >
> > v           ----------- 3           4           5
> >
> > I would want only
> >
> > v           ----------- 3                  5
> >
> > and then convert that into my interval type again for storage in a
> > result table in the format
> >
> > 3:5
> >
> > "Kent Tegels" wrote:
> >
> >> Hello Andy in S. Jersey Andy in S.,
> >>
> >>> I have a feeling that I can't do this, but thought someone might
> >>> have a way to work around this.
> >>>
> >> This seems to work. Consider:
> >>
> >> using System;
> >> using System.Data;
> >> using System.Data.SqlClient;
> >> using System.Data.SqlTypes;
> >> using Microsoft.SqlServer.Server;
> >> using System.IO;
> >> using System.Text.RegularExpressions;
> >> [Serializable]
> >> [SqlUserDefinedType(Format.UserDefined,IsByteOrdered=true,IsFixedLeng
> >> th=false,MaxByteSize=8000)]
> >> public class IntervalUDT : INullable, IBinarySerialize
> >> {
> >> static readonly Regex PARSER = new Regex(@"(\d*)\:(\d*)");
> >> int? Start = null;
> >> int? End = null;
> >> public override string ToString() {
> >> return string.Format("{0}:{1}",Start,End);
> >> }
> >> public bool IsNull {
> >> get {
> >> return (Start == null? true : false) ;
> >> }
> >> }
> >> public static IntervalUDT Null {
> >> get {
> >> IntervalUDT h = new IntervalUDT();
> >> return h;
> >> }
> >> }
> >> public static IntervalUDT Parse(SqlString s)
> >> {
> >> if (s.IsNull)
> >> return Null;
> >> IntervalUDT u = new IntervalUDT();
> >> string[] v = PARSER.Split(s.Value);
> >> u.Start = int.Parse(v[1]);
> >> u.End = int.Parse(v[2]);
> >> return u;
> >> }
> >> public void Read(BinaryReader r) {
> >> this.Start = r.ReadInt32();
> >> this.End = r.ReadInt32();
> >> }
> >> public void Write(BinaryWriter w) {
> >> w.Write((int)this.Start);
> >> w.Write((int)this.End);
> >> }
> >> public SqlInt32 GetStart() {
> >> if(Start == null)
> >> return SqlInt32.Null;
> >> else
> >> return new SqlInt32(this.Start.Value);
> >> }
> >> public SqlInt32 GetEnd() {
> >> if(End == null)
> >> return SqlInt32.Null;
> >> else
> >> return new SqlInt32(this.End.Value);
> >> }
> >> }
> >> and
> >>
> >> using System;
> >> using System.Data;
> >> using System.Data.SqlClient;
> >> using System.Data.SqlTypes;
> >> using Microsoft.SqlServer.Server;
> >> using System.Collections.Generic;
> >> using System.Collections;
> >> public partial class IntervalFunctions
> >> {
> >> [SqlFunction(FillRowMethodName="GetRows",TableDefinition="v int")]
> >> public static IEnumerable IntervalIntersect(IntervalUDT A,IntervalUDT
> >> B)
> >> {
> >> List<int?> alist = new List<int?>();
> >> List<int?> ilist = new List<int?>();
> >> for(int i = A.GetStart().Value;i <= A.GetEnd();i++)
> >> alist.Add(i);
> >> for(int i = B.GetStart().Value;i <= B.GetEnd();i++)
> >> if(alist.Contains(i))
> >> ilist.Add(i);
> >> return(ilist);
> >> }
> >> public static void GetRows(object o, out SqlInt32 v) {
> >> v = (o == null ? SqlInt32.Null : new SqlInt32((int)(o)) );
> >> }
> >> };
> >> This query seems to work:
> >>
> >> declare @u1 IntervalUDT
> >> declare @u2 IntervalUDT
> >> set @u1 = '2:4'
> >> set @u2 = '3:5'
> >> select * from dbo.IntervalIntersect(@u1,@u2)
> >> Cheers,
> >> Kent Tegels, DevelopMentor
> >> http://staff.develop.com/ktegels/
> ---
> Thanks,
> Kent Tegels, DevelopMentor
> http://staff.develop.com/ktegels/
>
>
>

13 Sep 2006 4:09 AM

Kent Tegels

Hello Andy in S. Jersey,

There's a huge "oh duh" look on my face. Naturally, my shortcut didn't consider
that. Unit Testing is your friend...

Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/