getitemvalue.sql

来自「财务管理系统 报账等基本功能实现 有完整的实现」· SQL 代码 · 共 33 行

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetItemValue]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetItemValue]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE GetItemValue @@Fm Varchar(255),@@nEmpId INT,@@dValue Decimal OUTPUT
 AS
    DECLARE @Rsum Decimal
    DECLARE @dSum Decimal
    DECLARE @vGa Varchar(100)

    SET  @vGa='基本工资'
    -- 取得此员工的基本工资
    SELECT @dSum=(SELECT Salary FROM Employees WHERE Empid=@@nEmpid)

    --将计算公式中的字符串替换为工资数值
    SET @@Fm=REPLACE(@@Fm,@vGa,LTRIM(RTRIM(STR(@dSum))))  
 
     DECLARE @sql nVarchar(4000)
     SET @sql='SELECT @@dValue='+LTRIM(RTRIM(@@Fm))
     -- 执行存储过程，取得公式的最终计算结果
     EXEC sp_executesql @sql,N'@@dValue decimal output',@@dValue OUTPUT
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO