建盛箱号存储过程

存储过程 JSRY_PrintBox

USE [Udit.CSR]
GO
/****** Object:  StoredProcedure [dbo].[JSRY_PrintBox]    Script Date: 2024/6/19 10:48:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




ALTER PROCEDURE [dbo].[JSRY_PrintBox]
    @BOX varchar(100)
AS
BEGIN
--SELECT @BOX BOX
declare @productInfor table(
       PN nvarchar(50),
       箱号 nvarchar(50),
       设置数量 nvarchar(50),
       ERP尺寸 nvarchar(50),
       实际称重 nvarchar(50),
       描述 nvarchar(50),
       单个净重 nvarchar(50),
       计算净重 nvarchar(50),
       标准尺寸 nvarchar(50),
       YYYYMMDD nvarchar(50),
       CPN nvarchar(50),
       实际数量 nvarchar(50),
       输入重量 nvarchar(50)
   )
declare @zhongliang nvarchar(50)
declare @z1 nvarchar(50)
declare @z2 nvarchar(50)

insert @productInfor 
SELECT top 1 a.[material_code] --PN
,[box_number] --箱号
,[box_size] --设置重量
,isnull(a.[DEA962],'') DEA962 --ERP尺寸
,isnull(CAST(a.[Weight] AS DECIMAL(18, 2)),0) AS Weight --实际称重
,m.[MaterialName] as 描述
,m.[DEA961] --单个净重
--,CAST((TRY_CONVERT(DECIMAL(18, 2), m.[DEA961]) * a.[box_total] * 0.001)AS DECIMAL(18, 2)) as 净重 --计算净重
,CASE WHEN m.[DEA961] IS NULL THEN 0 
      WHEN m.[DEA961] ='' THEN 0 
 ELSE CAST((TRY_CONVERT(DECIMAL(18, 2), m.[DEA961]) * a.[box_total] * 0.001) AS DECIMAL(18, 2)) END AS 计算净重 --计算净重
,m.[DEA962] as 标准尺寸
,(SELECT CONVERT(varchar, a.[create_time], 112)) AS YYYYMMDD
,isnull((SELECT TOP 1 [DEF003] FROM [Udit.Mms].[dbo].[KH_PN_file] where [DEF002]='TCL' and [DEF001]=a.[material_code] COLLATE Chinese_PRC_CI_AS),'ERROR') as C_PN
,a.[box_total] --实际数量
,a.[DEA961] as 输入重量
FROM [Udit.Mms].[dbo].[mms_boxs](nolock) a 
left join [Udit.Mms].[dbo].[mms_material] m on m.[MaterialCode]=a.[material_code] 
where [box_number]=@BOX

select @zhongliang=(case when CAST(输入重量 AS FLOAT) >0 then 输入重量 else 计算净重 end),@z1=输入重量,@z2=计算净重 from @productInfor

print '输入重量'
print @z1 
print '@z2'
print @z2
print 'zhongliang'
print @zhongliang


select *,(CPN+'*'+实际数量+'*109900*'+YYYYMMDD+'*'+实际称重+'*'+@zhongliang+'*'+ERP尺寸+'***'+箱号) as EWM from @productInfor



END