用户定义的标量函数以生成计算列 [英] user-defined scalar function to generate computed column

问题描述

我的数据库有2个表，即（Installation和InstallmentPlan）。
在分期付款表中有列数。我想添加一个新的计算列名称SurchargeCalculated（SC）。 SC的计算公式为

  SC =（安装数量。安装数量。天数*（安装数量。安装百分比/ 365/100） ）
  

我创建了用户定义的函数SurchargeCal，具有3个参数，分别是Amount，days和InstPercentage。问题是，当我在安装表中添加计算列并从那里调用标量函数时，saclar函数需要第二个表的InstPercetage参数（安装计划）。
我知道推荐的方法是使用视图，但这会使我的问题复杂化，因为我在C＃中使用分期付款表。

任何帮助将不胜感激。 / p>

我的标量函数是

  USE [myDB] 
 GO 
 / ******对象：UserDefinedFunction [dbo]。[SurchargeCal]脚本日期：17/02/2020 2:21:15 PM 
 ****** / 
在
 GO 
上设置ANSI_NULLS ON在
 GO 
上设置QUOTED_IDENTIFIER-===================== ====================== 
 ALTER FUNCTION [dbo]。[SurchargeCal] 
（
-为以下参数添加参数这里的函数
 @days为int，
 @amount为货币，
 @ surchargePerc为十进制
）
返回十进制
 AS 
开始
-在此处声明返回变量
 DECLARE @result为十进制= 0; 
 
-在此处添加T-SQL语句以计算返回值
 --SELECT< @ ResultVar，sysname，@ Result> =< @ Param1，系统名称，@ p1> 
 
如果@days = 0 
 set @result = 0 
 else if（@days> 0和@amount> 0）
 set @result =（ @days * @amount *（@ surchargePerc / 365/100））
其他
 set @result = 0 
 
 
-返回函数$的结果b $ b返回@结果
 
 END 
  

然后在添加表命令XXXX下面是问题

 使用[myDB] 
 GO 
 ALTER TABLE dbo。安装
添加SurchargeCalculated as dbo.SurchargeCalc（days，Amount，XXXX）-其中XXX应该是InstPercenatage 
 GO 
  

解决方案

由于您不能直接在计算列上使用子查询，因此需要在标量函数本身上进行操作：

 创建函数SurchargeCal（@days为整数，@ amount为货币，@ PlanKey为整数）
返回十进制
 AS 
 BEGIN 
 DECLARE @结果为十进制= 0; 
 
选择@结果= @数量* @天数* InstPercentage / 365/100 
从InstallMentPlan 
 WHERE PlanKey = @PlanKey 
 
 RETURN @结果
 END 
  

现在，您可以创建计算列，并传递PlanKey而不是InstPercentage。 / p>

  ALTER TABLE dbo。安装
添加附加费用AS dbo.SurchargeCalc（天数，金额，PlanKey）
  

I have databse having 2 tables i.e (Installment and InstallmentPlan). In the installment table there are number of columns. I want to add one new computed column name SurchargeCalculated (SC). The calculation for SC is this

SC = (Installment.Amount * Installment.days * (InstallmentPlan.InstPercentage /365 /100)) 

I have created user-defined function SurchargeCal having 3 parameters which are Amount, days and InstPercentage. The problem is that when i add computed column in installment table and call scalar function from there, the saclar func needs InstPercetage parameter of 2nd table (InstallmentPlan). I know recommended ways is to use view but that will complicate my problem as i am using installment table in C#.

Any help will be extremely appreciated.

My scalar function is

USE [myDB]
GO
/****** Object:  UserDefinedFunction [dbo].[SurchargeCal]    Script Date: 17/02/2020 2:21:15 PM 
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
ALTER FUNCTION [dbo].[SurchargeCal] 
(
    -- Add the parameters for the function here
    @days as int,
    @amount as money,
    @surchargePerc as decimal
)
RETURNS decimal
AS
BEGIN
    -- Declare the return variable here
    DECLARE @result as decimal =0;

    -- Add the T-SQL statements to compute the return value here
    --SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

        if  @days = 0
            set @result = 0
        else if (@days > 0 and @amount > 0)
            set @result = (@days * @amount * (@surchargePerc/ 365 / 100))
        else
            set @result = 0


    -- Return the result of the function
    RETURN @result

END

then below ADD table command XXXX is the problem

USE [myDB]
GO
ALTER TABLE dbo.Installment  
ADD SurchargeCalculated AS dbo.SurchargeCalc(days,Amount, XXXX) --where XXX should be InstPercenatage
GO

解决方案

Since you can't use a subquery directly on the computed column, you will need to do it on the scalar function itself :

CREATE FUNCTION SurchargeCal(@days as integer, @amount as money, @PlanKey as integer)
RETURNS decimal
AS
BEGIN
    DECLARE @result as decimal = 0;

    SELECT @result = @amount * @days * InstPercentage / 365 / 100
    FROM InstallMentPlan 
    WHERE PlanKey = @PlanKey    

    RETURN @result   
END

Now you can create the computed column, passing the PlanKey instead of its InstPercentage.

ALTER TABLE dbo.Installment  
ADD SurchargeCalculated AS dbo.SurchargeCalc(days, Amount, PlanKey)

这篇关于用户定义的标量函数以生成计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！