December 15, 2011 at 12:26 pm
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
December 15, 2011 at 12:43 pm
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
December 16, 2011 at 4:41 am
Almost at the bottom of the page.
December 16, 2011 at 5:35 pm
: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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply