SQL CLR FillRowMethodName

  • SeeCoolGuy

    SSCrazy

    Points: 2871

    I've been looking up different examples on the web including many of the posts in the forums and articles here on SSC, however, I'm still at a loss on how to get the things to work right. I can return a string function such as the following code

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString fngetFips(string strAddress, string strCity, string strState, string strZip, string strCountry)

    { string strFips;

    perfAddress.wsPA.sap_services ws = new perfAddress.wsPA.sap_services();

    strFips = ws.AddressToFIPS(strAddress, strCity, strState, strZip, strCountry).fips;

    // Put your code here

    return new SqlString(strFips);

    }

    that code works well, so I thought I would take on a TVF, but even though as I stated, I've followed the examples on this site along with others, Though I expected that I would get a two column single row, I instead get an error... (any advice?)

    Msg 6260, Level 16, State 1, Line 2

    An error occurred while getting new row from user defined Table Valued Function :

    System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Object[]'.

    System.InvalidCastException:

    at UserDefinedFunctions.getFillRow(Object obj, SqlString& strField1, SqlString& strField2)

    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "getFillRow",

    TableDefinition = "Field1 nvarchar(20), Field2 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,

    out SqlString strField2

    )

    {

    object[] row = (object[])obj;

    strField1 = (SqlString)row[0];

    strField2 = (SqlString)row[0];

    }

    -- Francisco

  • SeeCoolGuy

    SSCrazy

    Points: 2871

    Wow, I Kept fiddling with it and I can get it to work :w00t: .... but I still require some help 😀

    The code below creates a Single Column 2 Row result. How do I extend this to be 2x2?

    [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

  • okbangas

    SSChampion

    Points: 11773

    CLR Table-Valued Functions

    Almost at the bottom of the page.



    Ole Kristian VelstadbrÄten BangÄs - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • SeeCoolGuy

    SSCrazy

    Points: 2871

    :w00t: WOW... thank you so much.. I don't know why I couldn't see the forest for the trees, but WOW there it is!!! :w00t:

    for those interested parties, I have my working demo code that works nicely and even nicer in my actual functions in SQL!!

    thank you!

    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

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply