34,590
社区成员
发帖
与我相关
我的任务
分享
USE [OMS]
GO
/****** Object: StoredProcedure [dbo].[CC_test] Script Date: 06/07/2018 14:04:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[CC_test]
@OrderId nvarchar(20),
@DealerId nvarchar(20)
--,@UserId nvarchar(50)
,@OrderStatus nvarchar(20)
,@dtmAddStart datetime
,@dtmAddEnd datetime
AS
declare @t table(T1 varchar(10),T2 varchar(10))
declare @ID varchar(max)
declare @sql varchar(max)
set @ID=@OrderStatus
select @sql='select '''+REPLACE(REPLACE(SUBSTRING(@ID,1,LEN(@ID)-1),',',''','''),';',''' union all select ''')+''''
insert into @t exec(@sql)
BEGIN
----开始查询-----
Select top(100)
DealerId AS 经销商代码
, DealerShopId AS 经销商门店
, OrderNo AS 订单号
, (CASE OrderType WHEN 1 THEN 'A单'when 2 then 'B单'when 3 then 'C单'when 4 then '补单'when 5 then '售后单' ELSE'类型错误' end) AS 订单类型
, (CASE SplitBOMType WHEN 1 THEN '系统'when 2 then '手工' ELSE'类型错误' end) as 导入方式
, b.statename as 订单状态
, c.statename as 是否加急
, d.statename as 是否样品
, TotalArea AS 总面积
, Amount AS 订单金额
, OrderSplitBOMStatus as 解析状态
, Creater as 创建人
, CreatedDateTime as 创建时间
, UpdatedDateTime as 更新时间
, ClientName as 客户
, ClientPhoneNo as 客户电话
, ClientAddress as 客户地址
, DealerApprovedBy as 经销商确认人
, DealerRemark as 经销商备注
, OrderSubmitDateTime as 订单提交时间
, BOMApproveDateTime as 拆单审核时间
, BOMApproveName as 拆单审核人
, PriceApproveDateTime as 报价审核时间
, PriceApproveName as 报价审核人
, DealerConfirmDateTime as 经销商确认时间
, FinanceApproveDateTime as 财务审核时间
, FinanceApproveName as 财务审核人
, PlanAcceptDateTime as 计划接单时间
, PlanApproveDateTime as 计划审核时间
, PlanApproveName as 计划审核人
, ProductionStartDateTime as 开始生产时间
, FinishDateTime as 生产完成时间
, ShipmentDateTime as 入库时间
, Remark as 订单备注
, DesignApprovedBy as 设计审核人
, Explain1 as 说明1
, Explain2 as 说明2
from Z_Order a left join (select * from z_orderstate where pid =1 ) b on a.orderstatus=b.stateid
left join (select * from z_orderstate where pid =2 ) c on a.isurgent=c.stateid
left join (select * from z_orderstate where pid =3 ) d on a.issample=d.stateid
where OrderNo like isnull('%'+@OrderId+'%',OrderNo)
and DealerId like isnull('%'+@DealerId+'%',DealerId)
and OrderStatus in(SELECT T2 FROM @t where T1=1)
and IsUrgent in(SELECT T2 FROM @t where T1=2)--加急
and IsSample in(SELECT T2 FROM @t where T1=3)--样品
and CreatedDateTime >= @dtmAddStart and CreatedDateTime <= @dtmAddEnd+1
order by a.CreatedDateTime desc
END
USE [OMS]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].cc_test
@OrderId = N'',
@DealerId = N'',
@OrderStatus = N'1,2;1,1;2,0;2.1;',
@dtmAddStart = N'2018-06-01 16:37:35.797',
@dtmAddEnd = N'2018-06-09 16:37:35.797'
SELECT 'Return Value' = @return_value
GO
Select top(100)
DealerId AS 经销商代码
, DealerShopId AS 经销商门店
, OrderNo AS 订单号
, (CASE OrderType WHEN 1 THEN 'A单'when 2 then 'B单'when 3 then 'C单'when 4 then '补单'when 5 then '售后单' ELSE'类型错误' end) AS 订单类型
, (CASE SplitBOMType WHEN 1 THEN '系统'when 2 then '手工' ELSE'类型错误' end) as 导入方式
, b.statename as 订单状态
, c.statename as 是否加急
, d.statename as 是否样品
, TotalArea AS 总面积
, Amount AS 订单金额
, OrderSplitBOMStatus as 解析状态
, Creater as 创建人
, CreatedDateTime as 创建时间
, UpdatedDateTime as 更新时间
, ClientName as 客户
, ClientPhoneNo as 客户电话
, ClientAddress as 客户地址
, DealerApprovedBy as 经销商确认人
, DealerRemark as 经销商备注
, OrderSubmitDateTime as 订单提交时间
, BOMApproveDateTime as 拆单审核时间
, BOMApproveName as 拆单审核人
, PriceApproveDateTime as 报价审核时间
, PriceApproveName as 报价审核人
, DealerConfirmDateTime as 经销商确认时间
, FinanceApproveDateTime as 财务审核时间
, FinanceApproveName as 财务审核人
, PlanAcceptDateTime as 计划接单时间
, PlanApproveDateTime as 计划审核时间
, PlanApproveName as 计划审核人
, ProductionStartDateTime as 开始生产时间
, FinishDateTime as 生产完成时间
, ShipmentDateTime as 入库时间
, Remark as 订单备注
, DesignApprovedBy as 设计审核人
, Explain1 as 说明1
, Explain2 as 说明2
from Z_Order a
left join (select * from z_orderstate where pid =1 ) b on a.orderstatus=b.stateid
left join (select * from z_orderstate where pid =2 ) c on a.isurgent=c.stateid
left join (select * from z_orderstate where pid =3 ) d on a.issample=d.stateid
where OrderNo like isnull('%%',OrderNo)
and DealerId like isnull('%%',DealerId)
and OrderStatus in('1','2','3','4')
and IsUrgent in('0','1')--加急
and IsSample in('0')--样品
and CreatedDateTime >= '2018-06-01 16:37:35.797' and CreatedDateTime <= '2018-06-10 16:37:35.797'
select @sql='select '''+REPLACE(REPLACE(SUBSTRING(@ID,1,LEN(@ID)-1),',',''','''),';',''' union all select ''')+''''
--加上一行,
PRINT @sql;
insert into @t exec(@sql)
另外, 这种拼字符串然后又拆开的做法容易出问题(主要是你有两列在里面), 建议用表值参数, 这样清晰易懂, 不会出错。
https://blog.csdn.net/yenange/article/details/51488408