6

What I have is a CLR SqlFunction that produces 3 arrays of doubles. I would like this function to return something appropriate so that the FillRowMethod can output it for me as a table in T-SQL. It works fine for 1 array but I'm having trouble extending it to several. I'm mostly unsure on what to return from my method. Some code below:

[SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "FillRow",
    TableDefinition = "impliedVol float, maturity float, strike float")]
public static IEnumerable getStrippedCapletVolatilitiesFromCapVolatilityCurve(
     string uploadDate, double strike, double yearsForward, double intervalDuration,
     string curve, string surface)
    
//Create 3 arrays of doubles
    double[] array1;
    double[] array2;
    double[] array3;

    return [???];
}
    

public static void FillRow(object obj,
     out SqlDouble impliedVol, out SqlDouble maturity, out SqlDouble strike)
{
   //impliedVol = (double)obj;  //This is what I do if only returning one array
}

EDIT:

Based on feedback this is my new attempted solution.

public static IEnumerable getStrippedCapletVolatilitiesFromCapVolatilityCurve(string uploadDate, double strike, double yearsForward, double intervalDuration, string curve, string surface)
    {
            //omitted code above this line.
        CapletStipping thisCapletStripping = new CapletStipping(maturities, forwardRates, discountingRates, intervalDuration);
        double[][] theseStrippedCapletVols = thisCapletStripping.getCapletCurveForGivenStrike(flatVols, strike);

         List<capletVolatilityNode> capletVolatilitiesList = new List<capletVolatilityNode>(theseStrippedCapletVols[0].Length);

        for (int i = 0; i < theseStrippedCapletVols[0].Length; i += 1)
        {
            capletVolatilityNode thisCapletVolatilityNode = new capletVolatilityNode(theseStrippedCapletVols[0][i], theseStrippedCapletVols[1][i], theseStrippedCapletVols[2][i]);
            capletVolatilitiesList[i] = thisCapletVolatilityNode;
        }

        return capletVolatilitiesList; // theseStrippedCapletVols;
    }

    public class capletVolatilityNode
    {
        public double impliedVol;
        public double maturity;
        public double strike;
        public capletVolatilityNode(double impliedVol_, double maturity_, double strike_)
        {
            impliedVol = impliedVol_; 
            maturity = maturity_; 
            strike = strike_;
        }
    }

public static void FillRow(Object obj, out SqlDouble impliedVol, out SqlDouble maturity, out SqlDouble strike)
{

    capletVolatilityNode row = (capletVolatilityNode)obj;

    impliedVol = Convert.ToDouble(row.impliedVol);
    maturity = Convert.ToDouble(row.maturity);
    strike = Convert.ToDouble(row.strike);

}

1 Answer 1

6

If you are wanting to return the 3 arrays as 3 separate result sets, that is not possible for a function, whether SQLCLR or T-SQL. You would need to create a stored procedure instead in order to return multiple result sets.

If the 3 arrays represent 3 columns of a single result set such that they will all have the same number of items and the index value of one is conceptually associated with the same index value of the others (i.e. array1[x] is associated with array2[x] and array3[x], while array1[y] is associated with array2[y] and array3[y], and so on), then a simple array is the wrong type of collection. You can only return a single collection where each item/element in the collection represents a row in the result set (or at least enough info to construct the desired row). That singular collection, when returned from the SqlFunction method, is iterated over, calling the FillRowMethod for each item/element. One item/element is passed into the FillRowMethod which constructs the final result set structure and values and passes them back (hence you have the opportunity to create and/or translate values from the original item before passing them back).

In this latter case, you would need to create a class similar to:

private class volatility
{
    public double impliedVol;
    public double maturity;
    public double strike;
}

Then, create a generic list of those in your getStrippedCapletVolatilitiesFromCapVolatilityCurve method, add a new item to that collection for every row to return, and then return that list/collection. Your FillRowMethod will be called with the first parameter (as object) being of type volatility. That is where you will populate the out params from those properties of volatility. For example:

private static void FillRow(object obj,
     out SqlDouble impliedVol, out SqlDouble maturity, out SqlDouble strike)
{
    volatility row = (volatility)obj;

    impliedVol = new SqlDouble(row.impliedVol);
    maturity = new SqlDouble(row.maturity);
    strike = new SqlDouble(row.strike);
}

Now, it might be possible to handle this as a two-dimensional array (i.e. double[][]) that you return from the main SqlFunction, but then the FillRow method would be sent a double[] since the first dimension is broken out into individual calls to the FillRow method. I've never tried this specific approach, but it should work as follows:

private static void FillRow(object obj,
     out SqlDouble impliedVol, out SqlDouble maturity, out SqlDouble strike)
{
    double[] row = (double[])obj;

    impliedVol = new SqlDouble(row[0]);
    maturity = new SqlDouble(row[1]);
    strike = new SqlDouble(row[2]);
}

ALSO:

It just occurred to me that you could even forgo the generic list / collection and stream the contents of the double[][] array out to the result set, one item / row at a time. Go ahead and try this:

public static IEnumerable getStrippedCapletVolatilitiesFromCapVolatilityCurve(...)
{
    //omitted code above this line.
    CapletStipping thisCapletStripping =
       new CapletStipping(maturities, forwardRates, discountingRates, intervalDuration);
    double[][] theseStrippedCapletVols =
       thisCapletStripping.getCapletCurveForGivenStrike(flatVols, strike);

    // THIS PART IS DIFFERENT -- begin
    capletVolatilityNode thisCapletVolatilityNode = new capletVolatilityNode();

    for (int i = 0; i < theseStrippedCapletVols[0].Length; i += 1)
    {
        thisCapletVolatilityNode.impliedVol = theseStrippedCapletVols[0][i];
        thisCapletVolatilityNode.maturity = theseStrippedCapletVols[1][i];
        thisCapletVolatilityNode.strike = theseStrippedCapletVols[2][i];

        yield return thisCapletVolatilityNode; // return rows individually
    }

    return; // cannot return anything when using "yield return"
    // THIS PART IS DIFFERENT -- end
}

private class capletVolatilityNode
{
    public double impliedVol;
    public double maturity;
    public double strike;
}

There are some restrictions when using yield return, but if your process allows for this construct, then this will not only be faster, but also take up less memory. These benefits are due to this code skipping the step of copying the results of the getCapletCurveForGivenStrike() method to a separate collection (i.e the Generic List) just to return it to T-SQL (in which case you need to wait while it copies the collection and consumes more memory).


On a related note: use the Sql* types for the input parameters instead of standard .NET types. Meaning, use SqlString instead of string and SqlDouble instead of double. You can then easily get the .NET native type from those via the Value property that all of the Sql* types have (e.g. SqlString.Value passes back a string, etc).

For more info on working with SQLCLR in general, please visit: SQLCLR Info

14
  • Hi, no not quite I just want to return them as 1 result set. I have rewritten the code a bit so that I instead have my results (that I want in an SQL table) as a 2 dimensional array of doubles, double[][] outPutArray that is returned by my "Microsoft.SqlServer.Server.SqlFunction" function. I still have trouble building my FillRow function however to make it fill the rows of my table with the data in my two dimensional array.
    – Oscar
    Oct 9, 2020 at 12:45
  • @Oscar I have updated my answer. Please review. Oct 9, 2020 at 14:26
  • 1
    Hi, I updated my post with my new code based on your feedback. It is giving me some index out of bounds error that I'm still trying to figure out, but is this along the lines of what you meant?
    – Oscar
    Oct 9, 2020 at 14:54
  • 1
    @SolomonRutzky Ah yes I see it now. Something like that is what I first attempted (but I didn't think about the fact that the first dimension is broken out, so I tried to to do double[][] row = (double[][])obj; and went crazy not understanding why it gave me an error. I do think it should work probably if you do it your way, to me it makes a lot of sense. I will give it a try on monday. And thank you for the suggestions on sqldouble etc. I will make sure to fix that. It's supposed to be capletstRipping, I must have made a typo in the function name that has gone unnoticed.
    – Oscar
    Oct 10, 2020 at 10:16
  • 1
    @Oscar That must be the "omitted code" section at the top of that method. I did some testing and found the problem: you're using context connection = true;, which is usually preferred but doesn't allow doing yield return;. So, you can either a) stick with the original code using the in-process connection and not stream the results back, or b) switch to using a standard / external connection which connects as a separate session (more memory + CPU used plus no access to current transaction / temp objects and can't be a SAFE assembly) but can stream results back. Oct 15, 2020 at 5:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.