[dba-VB] Sql Server 2008 VTF and SQL CLR

this works now...

working demo code:



    private class myRows
    {
        public SqlInt32 PKID;
        public SqlString strValue;

        public myRows(SqlInt32 pkid, SqlString strvalue)
        {
            PKID = pkid;
            strValue = strvalue;
        }
    }
    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName =
"getFillRow",
     TableDefinition = "pkid int,Field1 nvarchar(20)")]
    public static IEnumerable fngetTEST(string strSomeInput)
    {

        ArrayList myArray = new ArrayList();

        myArray.Add(new myRows(0, "Field1"));
        myArray.Add(new myRows(1, "Field2"));
        myArray.Add(new myRows(2, "Field3"));
        myArray.Add(new myRows(3, "Field4"));


        return new ArrayList(myArray);

    }
    private static void getFillRow(
        object arrayObj,
        out SqlInt32 intPKID,
        out SqlString strField1
        )
    {
        myRows MyRows = (myRows)arrayObj;
        intPKID = MyRows.PKID;
        strField1 = MyRows.strValue;

    }



-Francisco
http://bit.ly/sqlthis   | Tsql and More...
<http://db.tt/JeXURAx>




On Thu, Dec 15, 2011 at 11:48, Francisco Tapia <fhtapia at gmail.com> wrote:

> have any of you worked with Table Valued Functions yet?  I know how to
> create a SQL value table function in pure TSQL, but getting one to work
> that's built off of SQL CLR is something new entirely to me  :phew:  My
> sample here is simple, but the idea is that I'm going to be calling a
> webservice to collect some data.  I'd like to have at least a two column
> table returned so that I can create labels or maybe a multi-column with the
> appropriate column name.
>
> here is my working code from c# and this does yield me a working returned
> table.
>
> As always your help is appreciated!
>
>   [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "getFillRow",
>      TableDefinition = "Field1 nvarchar(20)")]
>     public static IEnumerable fngetTEST(string strSomeInput)
>     {
>         ArrayList myList = new ArrayList();
>         myList.Add("test1");
>         myList.Add("test2");
>         return new ArrayList(myList);
>     }
>     private static void getFillRow(object obj,
>         out SqlString strField1
>
>         )
>     {
>         strField1 = Convert.ToString(obj);
>     }
>
>
> -Francisco
> http://bit.ly/sqlthis   | Tsql and More...
> <http://db.tt/JeXURAx>
>
>
>