注册

SqlServer存储过程中标量值函数的写法

2025/04/11 219 1

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:8
【操作系统】:win 10
【CPU】:英特尔
【问题描述】*:SqlServer存储过程中标量值函数的写法
原sql:
CREATE PROCEDURE [futian_user].[PROC_FP_GetFPINFO]
@SJH VARCHAR(15),
@TJBH VARCHAR(10),
@TJCS INT,
@FPHM_I VARCHAR(20),
@DYLX VARCHAR(1)
AS
BEGIN
declare @FPMC varchar(100)
SELECT @FPMC= isnull(MC,'') from MZ_XTCS where zdm ='BS_ZCMC_LYL'
declare @JKR varchar(20)
SELECT @JKR= XM from TJ_TJDJB where TJBH=@TJBH AND TJCS=@TJCS
declare @KPRQ datetime;
select @KPRQ = GETDATE();
declare @QTXX varchar(500)
declare @SSJE decimal(18,4)
declare @SKR varchar(20)
declare @SSJE_DX varchar(200)

select @SSJE=CONVERT(decimal(18,2),A.JE),@QTXX=A.FPQTXX,@SKR=ISNULL(B.XM,A.CZY) from TJ_SJH_FP A
LEFT JOIN GZRY B ON a.CZY = b.GKHM
where A.FPXH=@FPHM_I

create table #FPINTO (
FPMC varchar(100),
PJDM varchar(100),
PJHM varchar(100),
SHXYDM varchar(100),
JYM varchar(100),
JKR varchar(100),
KPRQ datetime,
JEHJ_DX varchar(100),
JEHJ_XX decimal(18,4),
QTXX varchar(100),
SKDW varchar(100),
FHR varchar(100),
SKR varchar(100)
)

select @SSJE_DX = [futian_user].f_num_chn
insert into #FPINTO values (@FPMC,'',@FPHM_I,'','',@JKR,@KPRQ,@SSJE_DX,@SSJE,@QTXX,@FPMC,@SKR,@SKR)
select *from #FPINTO
drop table #FPINTO

END

转换后的sql:
CREATE PROCEDURE "futian_user"."PROC_FP_GETFPINFO"

/DMDTS CONVERT
CREATE PROCEDURE [futian_user].[PROC_FP_GetFPINFO]
/
(
DM_SJH VARCHAR (15),
DM_TJBH VARCHAR (10),
DM_TJCS INT ,
DM_FPHM_I VARCHAR (20),
DM_DYLX VARCHAR (1)
)
AS
DM_FPMC varchar (100);

DM_JKR varchar (20);

DM_KPRQ TIMESTAMP ;

DM_QTXX varchar (500);

DM_SSJE decimal (18,4);

DM_SKR varchar (20);

DM_SSJE_DX varchar (200);

BEGIN
/DMDTS CONVERT
DM_FPMC varchar (100)
/

SELECT isnull(MC,'') INTO DM_FPMC from MZ_XTCS where ZDM ='BS_ZCMC_LYL';
/DMDTS CONVERT
DM_JKR varchar (20)
/

SELECT XM INTO DM_JKR from TJ_TJDJB where TJBH=DM_TJBH AND TJCS=DM_TJCS;
/*DMDTS CONVERT
DM_KPRQ TIMESTAMP */

select GETDATE() INTO DM_KPRQ ;
/DMDTS CONVERT
DM_QTXX varchar (500)
/

/DMDTS CONVERT
DM_SSJE decimal (18,4)
/

/DMDTS CONVERT
DM_SKR varchar (20)
/

/DMDTS CONVERT
DM_SSJE_DX varchar (200)
/

select CONVERT( decimal(18,2),A.JE),A.FPQTXX,ISNULL(B.XM,A.CZY) INTO DM_SSJE,DM_QTXX,DM_SKR from TJ_SJH_FP A
LEFT JOIN GZRY B ON A.CZY = B.GKHM
where A.FPXH=DM_FPHM_I; EXECUTE IMMEDIATE'

create table #FPINTO (
FPMC varchar (100),
PJDM varchar (100),
PJHM varchar (100),
SHXYDM varchar (100),
JYM varchar (100),
JKR varchar (100),
KPRQ TIMESTAMP ,
JEHJ_DX varchar (100),
JEHJ_XX decimal (18,4),
QTXX varchar (100),
SKDW varchar (100),
FHR varchar (100),
SKR varchar (100)
)';

select ""."F_NUM_CHN"(DM_SSJE) INTO DM_SSJE_DX ;
insert into #FPINTO values (DM_FPMC,'',DM_FPHM_I,'','',DM_JKR,DM_KPRQ,DM_SSJE_DX,DM_SSJE,DM_QTXX,DM_FPMC,DM_SKR,DM_SKR);
select *from #FPINTO; EXECUTE IMMEDIATE'
drop table #FPINTO';

END;
这一行应该如何转换“select ""."F_NUM_CHN"(DM_SSJE) INTO DM_SSJE_DX ; ”
F_NUM_CHN 是一个标量值函数
求解

回答 0
暂无回答
扫一扫
联系客服