注解:--
之后可以跟上表名填上参数,选中直接执行。(之前对这个不熟悉,记录下来以免忘记)
eg.:
USE [PPP]
GO
/****** Object: StoredProcedure [dbo].[test_roughGrindingYieldMonthReportLaiLiaoJiaGong] Script Date: 03/14/2020 09:10:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- m.board_type not in (''9'')
-- test_roughGrindingYieldMonthReportLaiLiaoJiaGong '2020-03-01','2020-03-14','1'
--
ALTER PROCEDURE [dbo].[test_roughGrindingYieldMonthReportLaiLiaoJiaGong]
@startDate varchar(20),
@endDate varchar(20),
@factory varchar(2)
AS
BEGIN
declare @strWhere1 varchar(200)
IF (@factory IS NOT NULL and @factory<>'')
SET @strWhere1 = ' and o.factory= ''' + @factory + ''' '
else
SET @strWhere1 = ' and 1 = 1 '
EXEC ('select m.jumbo_plates_id as jumboId, m.jumbo_plates_type as jumboDesc, m.jumbo_plates_thickness as thickness,
isnull(dev9.sqm,0)+isnull(dev99.sqm,0) as sqm9, isnull(dev9.mt,0)+isnull(dev99.mt,0) as mt9
from jumbo_plates_main as m
/* 来料加工 */
left join(select n.jumbo_id, sum(n.total_sqm) as sqm, sum(n.total_weight) as mt from
(select o.jumbo_id, o.total_sqm, o.total_weight from online_record_shift as o
where o.input_date>=''' + @startDate + ''' ' + @strWhere1 + ' and o.input_date<=''' + @endDate + ''' and SUBSTRING(o.jumbo_id,1,2)=''LL'' and o.last_processing_number in (''20'',''30'',''40'',''50'',''60'')
and o.processing_number not in (''20'',''30'',''40'',''50'',''60'')
and o.last_processing_number<>o.processing_number) as n group by n.jumbo_id) as dev9
on(dev9.jumbo_id=m.jumbo_plates_id)
left join(select n.material_number, sum(n.total_sqm) as sqm, sum(n.total_weight) as mt from
(select o.processing_order_number,
o.processing_order_item_number, o.stock_out_quantity, i.material_number, c.sqm_perpiece, i.material_thickness,o.total_sqm, o.total_weight from finished_product_stock_out as o
inner join(select processing_order_number, processing_order_item_number, group_id, material_number, sqm_perpiece, material_thickness from processing_order_items where SUBSTRING(material_number,1,2)=''LL'') as i
on(i.processing_order_number=o.processing_order_number and i.processing_order_item_number=o.processing_order_item_number)
inner join(select processing_order_number, processing_order_item_number, sqm_perpiece from processing_product )as c
on(c.processing_order_number=o.processing_order_number and c.processing_order_item_number=o.processing_order_item_number)
inner join(select processing_order_number from processing_orders where invoice_area=''2'') as p
on(p.processing_order_number=o.processing_order_number)
where o.stock_out_date>=''' + @startDate + ''' and o.stock_out_date<=''' + @endDate + ''' and o.withdraw_flag=''0'' ' + @strWhere1 + ') as n group by n.material_number) as dev99
on(dev99.material_number=m.jumbo_plates_id)
order by m.jumbo_plates_id')
END