如何为我的SQL CLR运行总函数创建重置?

本文关键字：创建函数运行我的 SQL CLR | 更新日期: 2023-09-27 18:10:50

我正在尝试创建一个SQL CLR函数，执行类似于这里解释的运行总数:SQL Server和使用CLR更新的最快运行总数

但是，这个函数只计算一个列中所有值的总和。我要做的是重置运行总数，以便每当field1 (SQL varchar)和field2 (SQL numeric)之间有转换时，它就变为0。然而，我似乎无法找出必要的代码来做到这一点。我已经尝试使用CallContext存储这两个字段，但我一直运行到空引用。我认为转换检测的工作原理与运行总数计算类似，但事实并非如此。我已经绞尽脑汁想了好几个小时了，还是没找到。

这是我使用这个函数的表，主键是REF_NO:

CREATE TABLE [dbo].[USER_TB_TIME_TICKETS] ( [REF_NO] [dbo].[T_DOC_NO] NOT NULL, [REF_DAT] [dbo].[T_DAT_SMALL] NOT NULL, [ACT_DAT] [dbo].[T_DAT_SMALL] NOT NULL, [USR_ID] [dbo].[T_USR_ID] NOT NULL, [CUST_NO] [dbo].[T_CUST_NO] NOT NULL, [JOB_NO] [dbo].[USER_TB_T_JOB_NO] NOT NULL, [CODE] [dbo].[USER_TB_T_SERV_COD] NOT NULL, [HRS_WORKED] [dbo].[USER_TB_T_HOURS] NOT NULL, [DESCR] [varchar](200) NOT NULL, [MILEAGE] [dbo].[USER_TB_T_MILES] NULL, [NOTES] [dbo].[USER_TB_T_NOTE] NULL, [BILL_FLG] [varchar](1) NULL, [BILL_HRS] [decimal](6, 2) NULL, [EXCESS_HRS] [decimal](6, 2) NULL, )

下面是SQL标量CLR函数包装器:

CREATE FUNCTION [dbo].[fn_RunningTotalDecimal_15_2_ResetStringNumeric]( @val [decimal](15, 2), @id [tinyint], @rowNo [int], @nullValue [decimal](15, 2), @field1 [nvarchar](15), @field2 [numeric](18, 0)) RETURNS [decimal](15, 2) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SqlClrRunningTotals].[RDSAPI.SQLClrRunningTotals.RunningTotals].[RunningTotalDecimalResetStringNumeric]

下面是c#代码:

/// <summary>
    /// Storage Structure for holding actual Total and row number for security check.
    /// </summary>
    /// <typeparam name="T">Totals Data Type</typeparam>
    private struct RtStorage<T> where T : struct
    {
        public T Total;
        public int RowNo;
    }
    private struct StringFieldStorage<T> where T : struct
    {
        public T stringField;    
    }
    private struct NumericFieldStorage<T> where T : struct
    {
        public T numericField;
    }

....Other extraneous class code...

 /// <summary>
    /// Calculates a running totals on Decimal data type based on transistion between a string and numeric field.
    /// </summary>
    /// <param name="val">Value of current row</param>
    /// <param name="id">ID of the function in single query</param>
    /// <param name="rowNo">Specifies expecter rowNo. It is for security check to ensure correctness of running totals</param>
    /// <param name="nullValue">Value to be used for NULL values</param>
    /// <param name="field1">String field</param>
    /// <param name="field2">Numeric field</param>
    /// <returns>SqlDecimal representing running total</returns>
    [SqlFunction(IsDeterministic = true)]
    public static SqlDecimal RunningTotalDecimalResetStringNumeric(SqlDecimal val, SqlByte id, int rowNo, SqlDecimal nullValue, SqlString field1, SqlDecimal field2)
    {
        string dataName = string.Format("MultiSqlRt_{0}", id.IsNull ? 0 : id.Value);
        string field1Name = string.Format("MultiSqlField1_{0}", id.IsNull ? 0 : id.Value);
        string field2Name = string.Format("MultiSqlField2_{0}", id.IsNull ? 0 : id.Value);
        object lastSum = CallContext.GetData(dataName);
        object field1Value = CallContext.GetData(field1Name);
        object field2Value = CallContext.GetData(field2Name);
        var storage = lastSum != null ? (RtStorage<SqlDecimal>)lastSum : new RtStorage<SqlDecimal>();
        storage.RowNo++;
        var stringFieldStorage = field1Value != null ? (StringFieldStorage<SqlString>)field1Value : new StringFieldStorage<SqlString>();
        var numericFieldStorage = field2Value != null ? (NumericFieldStorage<SqlDecimal>)field2Value : new NumericFieldStorage<SqlDecimal>();
        if (storage.RowNo != rowNo)
            throw new System.InvalidOperationException(string.Format("Rows were processed out of expected order. Expected RowNo: {0}, received RowNo: {1}", storage.RowNo, rowNo));
        if (stringFieldStorage.stringField != field1 || (stringFieldStorage.stringField == field1 && numericFieldStorage.numericField != field2))
        {
            storage.Total = new SqlDecimal(0);
            stringFieldStorage.stringField = field1;
            numericFieldStorage.numericField = field2;
        }
        if (!val.IsNull)
            storage.Total = storage.Total.IsNull ? val : storage.Total + val;
        else
            storage.Total = storage.Total.IsNull ? nullValue : (nullValue.IsNull ? storage.Total : storage.Total + nullValue);
        CallContext.SetData(dataName, storage);
        CallContext.SetData(field1Name, stringFieldStorage);
        CallContext.SetData(field2Name, numericFieldStorage);
        return storage.Total;
    }

编辑

这里是一个图像，使它更清楚一点。(兴趣栏被盯着看。我想放一张图片，但我没有足够的代表性)。

<>之前CUST_NO、JOB_NO USR_ID, REF_NO、REF_DAT ACT_DAT, HRS_WORKED, HOURS_QUOTED, BILL_FLG, BILL_HRS, EXCESS_HRS running_totalATA 1 AML, 152364, 2015-06-18, 2015-06-16, 0.25, 12.00,, 0.25, 0.00, 9.50ATA, AMA, 152367, 2015-06-18, 2015-06-18, 0.25, 12.00,, 0.25, 0.00, 9.75ATA 1 AML, 152372, 2015-06-18, 2015-06-18, 1.50, 12.00,, 1.50, 0.00, 11.25ATA, AMA, 152569, 2015-06-22, 2015-06-22, 0.50, 12.00,, 0.50, 0.00, 11.75ATA, AMA, 152735, 2015-06-25, 2015-06-25, 0.50, 12.00,, 0.25, 0.25, 12.25* * ATA * *, * * 1 * *, AMA, 153472, 2015-07-14, 2015-07-13, 0.25, 12.00, 0.00, 0.25, 12.50 * * * ** * ATA * *, * * 2 * *, SCP, 152097, 2015-06-12, 2015-06-10, 0.50, 3.00,, 0.50, 0.00, 13.00 * * * *ATA 2 CTK, 151923, 2015-06-11, 2015-06-11, 0.75, 3.00,, 0.75, 0.00, 13.75ATA 2 CTK, 151998, 2015-06-12, 2015-06-12, 0.75, 3.00,, 0.75, 0.00, 14.50之前

我想做的是改变c#代码，这样我就可以检测CUST_NO或JOB_NO之间的变化(如果CUST_NO是相同的)，并在转换时将运行的total列重置为0。实际上，我想对每个JOB_NO和每个CUST_NO进行合计。我意识到这是一个分组函数，但我使用CLR函数，因为在SQL中有效地计算运行总数是困难的，而且我将对一个有140,000多条记录的表执行此操作，该表每天都在增长。由于我使用的是SQL Server 2008 R2，所以我在2012年没有得到windows ROWS功能，所以我可以获得足够的读取来运行此数据上的报告的唯一方法是使用CLR函数。本文解释了性能测试:运行总计的最佳方法——针对SQL Server 2012更新