🌈 MsSql
동적 쿼리문 예제
James Wetzel
2020. 6. 1. 14:03
728x90
반응형
declare
@picking_no bigint = ''
, @pl_cd varchar(10) = ''
, @date_type int = 1
, @start_date datetime = '2020-5-31'
, @end_date datetime = '2020-5-31'
, @search_type int = 0
, @search_value varchar(25) = ''
, @picking_status char(1) = 'A'
begin
declare @sql nvarchar(max)
, @paramlist nvarchar(4000)
set @sql = N'
set nocount on
declare @datetime_min_value datetime = ''1753-01-01''
if @end_date > @datetime_min_value
begin
set @end_date = dateadd(millisecond, -3, dateadd(day, 1, @end_date))
end
select
pl.picking_no
, max(pl.op_id) as op_id
, max(pl.picking_yn) as picking_yn
, max(pl.reg_id) as reg_id
, max(pl.reg_dt) as reg_dt
, max(pl.cart_no) as cart_no
, max(cb.cd_nm) as picking_status
from
inner join
on ps.picking_no = pl.picking_no
left join
on cb.cd_type = ''WMS007''
and cb.svc_nation_cd = ''SG''
and ps.picking_yn = cb.cd
where pl.pl_cd = @pl_cd
'
if @picking_no > 0
begin
set @sql = @sql + N' and pl.picking_no = @picking_no '
end
if @date_type = 1
begin
set @sql = @sql + N' and pl.reg_dt between @start_date and @end_date '
end
if @search_type = 1
begin
set @sql = @sql + N' and pl.op_id = @search_value '
end
if @search_type = 2
begin
set @sql = @sql + N' and ps.sku_no = @search_value '
end
if @picking_status <> 'A'
begin
set @sql = @sql + N' and pl.picking_yn = @picking_status '
end
set @sql = @sql + N'
group by pl.picking_no
order by pl.picking_no desc
'
set @paramlist = N'
@picking_no bigint
, @pl_cd varchar(10)
, @date_type int
, @start_date datetime
, @end_date datetime
, @search_type int
, @search_value varchar(25)
, @picking_status char(1)
'
exec sp_executesql @sql
, @paramlist
, @picking_no = @picking_no
, @pl_cd = @pl_cd
, @date_type = @date_type
, @start_date = @start_date
, @end_date = @end_date
, @search_type = @search_type
, @search_value = @search_value
, @picking_status = @picking_status
end
728x90
반응형