동적 쿼리문 예제

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