Use [Db_yh_erp_gw_v2_newreq]
GO
/****** object:storedprocedure [dbo]. [Sp_factoryallocationfind] Script date:05/09/2016 08:45:52 ******/
SET ANSI_NULLS on
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
--Author: <Author,,Name>
--Create Date: <create date,,>
--Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo]. [Sp_factoryallocationfind]
--[Business Date]
@btime datetime,
@etime datetime,
--[Factory]
@warehouseOut varchar (50),
--[transfer into the factory]
@warehouseIn varchar (50),
--[Allocation number]
@sn varchar (50),
--[Transfer Inbound number]
@snIn varchar (50),
--[transfer cancellation tracking number]
@snCancel varchar (50),
--[description]
@remark varchar (200),
--[Package Information]
@package varchar (200),
--[status]
@status varchar (50),
--[Approval Status]
@auditStatus varchar (50),
--[not processed in transit]
@intransit bit
As
BEGIN
Declare @sqltable table (F_SN varchar (50))--This uses a temporary table
If (@snIn is not null) or (@intransit are NOT null)
Insert into @sqltable (F_SN) (select F_ALLOCATIONSN from dbo. T_warehouse_factoryallocationin
where f_sn = @snIn or f_intransitstatus = @intransit)--Storage of temporary tables
else if (@snCancel is not NULL)
Insert into @sqltable (F_SN) (select F_ALLOCATIONSN from dbo. T_warehouse_factoryallocationcancel
where f_sn = @snCancel)
else if (@package is not NULL)
Insert into @sqltable (F_SN) (select F_sn from dbo. T_warehouse_factoryallocation
where f_id = (
Select F_allocationid from dbo. T_warehouse_factoryallocationdetail
where CHARINDEX (@package, f_version) >0 or CHARINDEX (@package, F_packagebarcode) >0 or CHARINDEX (@package, f_ ProductName) >0))
Else
Insert into @sqltable (F_SN) (select F_sn from dbo. T_warehouse_factoryallocation
Where (ISNULL (@btime, ') = ' or f_servicetime>[email protected])
and (ISNULL (@etime, ") =" or f_servicetime< DATEADD (dd,1, @etime))
and (ISNULL (@warehouseOut, ") =" or CHARINDEX (@warehouseOut, f_outfactoryname) >0)
and (ISNULL (@warehouseIn, ") =" or CHARINDEX (@warehouseIn, f_infactoryname) >0)
and (ISNULL (@sn, ") =" or CHARINDEX (@sn, F_SN) >0)
and (ISNULL (@remark, ") =" or CHARINDEX (@remark, F_remark) >0)
and (ISNULL (@status, ") =" or CHARINDEX (@status, f_status) >0)
and (ISNULL (@auditStatus, ") =" or CHARINDEX (@auditStatus, F_status) >0))
SELECT *
From
Select f_id as ID, ' recall ' as type, F_SN as transfer number, F_SN as number, f_servicetime as business date, f_outfactoryname as Call factory, F_outwarehousena Me as pull out the warehouse,
F_infactoryname as is transferred into the factory, F_inwarehousename as into the warehouse, f_creationtime as creation time, F_creator as creator, f_status as state,
F_auditstatus as Approval Status
FROM dbo. T_warehouse_factoryallocation
Union
Select f_id as ID, ' call in ' as type, F_ALLOCATIONSN as transfer number, F_SN as number, f_servicetime as business date, ' as call-up factory, ' as recall warehouse,
' as transferred into the factory, ' as in the warehouse, f_creationtime as creation time, F_creator as creator, f_status as state,
F_auditstatus as Approval Status
FROM dbo. T_warehouse_factoryallocationin
Union
Select f_id as ID, ' Cancel ' as type, F_ALLOCATIONSN as transfer number, F_SN as number, f_servicetime as business date, ' as call-up factory, ' as call-up warehouse,
' as transferred into the factory, ' as in the warehouse, f_creationtime as creation time, F_creator as creator, f_status as state,
F_auditstatus as Approval Status
FROM dbo. T_warehouse_factoryallocationcancel
) as T
where T. Allocation number = Any (select * from @sqltable)--use of temporary tables
Order by T. Allocation number, T. type
END
An example of a complex SQL stored procedure
