Difficult for help, can't figure out the idea, let's look at it

muxiaoke

Don't simply talk about using dynamic SQL statements. The previous elder brother has already told me and I understand, but I still haven't figured out a way to solve this problem. Please master the following carefully:

There is a stored procedure with 3 input parameters, which are @companname, @startdate, @enddate; representing the company name, start time, end time, the current requirements are:
Each of these three parameters has two states: empty and non-empty. When combined, there are N kinds of results. If you write them one by one, it should be more troublesome. Ask the master to see how to write this stored procedure.

The following is all not empty
——————————————————————————————————
            select company id, company name, sum (amount) as total from
                  (
                   select * from 1 where request time <@enddate and request time> @startdate
                   union all
                   select * from 2 where request time <@enddate and request time> @startdate
                  ) t where approval opinion = 'agree' and company name = @companyname
           group by company id, company name order by company id
——————————————————————————————————


The following is all empty
——————————————————————————————————
            select company id, company name, sum (amount) as total from
                  (
                   select * from 1
                   union all
                   select * from 2) t where approval opinion = 'agree' group by company id, company name order by company id
——————————————————————————————————

May also need to pay attention to determine whether the start time and end time are correct, please help me!

恶魔右指

where request time between @startdate and @enddate

恶魔右指

Introduce usage:
In this way, you need to add hours, such as: 2007-01-01 00:00:00 —2007-01-01 23:59:59
Variable type is datetime
set @ startdate = '2007-01-01 00:00:00'
set @ enddate = '2007-01-01 23:59:59
where request time between @startdate and @enddate
Variable type is varchar
set @startdate = '20070101'-take days as the unit
set @enddate = '20070101'
where convert (varchar (8), request time, 112) between @startdate and @enddate

muxiaoke

Thanks to the elder brother upstairs for the explanation, but the question now is how to write a dynamic sentence to judge whether these three fields are empty or not?

pupupu01

create oric test
(
@companyname varchar (100) = null,
@enddate datetime = null,
@startdate datetime = null)
as
begin

select company id, company name, sum (amount) as total from
(
                   select * from [1]
                   union all
                   select * from [2]
) t
where approval opinion = 'agree'
and company name = (case when @companyname is null then company name else @companyname end)
and request time <(case when @enddate is null then request time + 1 else @enddate end)
and request time> (case when @startdate is null then request time-1 else @startdate end)
group by company id, company name order by company id

end

This way it doesn't matter whether you pass a few variables, empty or not.

If only one parameter is passed:

exec test @ companyname = 'Microsoft Corporation'

pupupu01

create oric test ---> create proc test

waxiaoning

Man, not many combinations
declare @sql varchar (8000)
declare @f varchar (400)
set @ f = (case when @enddate is null then '' else '
where request time <'' '+ @ enddate +' '' 'end) +
(case when @startdate is null then '' else 'and request time>' '' + @ startdate + '' '' end) + ''
set @ sql = 'select company id, company name, sum (amount) as total from
                  (
                   select * from 1 '+ @ f +
'union all
                   select * from 2 '+ @ f +
                  ') t where approval opinion = `` agree' '' +
(case when isnull (@corpname, '') = '' then '' else 'and company name =' '' + @ companyname + '' '' end) + '
           group by company id, company name order by company id '
exec (@sql)

waxiaoning

pupupu01is good

waxiaoning

9988 Good

caboom04

Thank you guys upstairs, go back and study carefully, reply tomorrow