Solve a multi-condition stored procedure that determines whether each condition is empty and then decides which step to

caboom04

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!

小老鹰

Use dynamic SQL.

Such as:

declare @ p1 int, @ p2 int, @ p3 int, @sql varchar (8000)

set @sql = 'select * from table name where 1 = 1'
if @ p1 is not null
    set @sql = @sql + 'and field 1 =' + cast (@ p1 as varchar (100))

if @ p2 is not null
    set @sql = @sql + 'and field 1 =' + cast (@ p2 as varchar (100))

if @ p3 is not null
    set @sql = @sql + 'and field 1 =' + cast (@ p3 as varchar (100))

exec (@sql)

小老鹰

Such as:

declare @ p1 int, @ p2 int, @ p3 int, @sql varchar (8000)

set @sql = 'select * from table name where 1 = 1'
if @ p1 is not null
    set @sql = @sql + 'and field 1 =' + cast (@ p1 as varchar (100))

if @ p2 is not null
    set @sql = @sql + 'and field 2 =' + cast (@ p2 as varchar (100))

if @ p3 is not null
    set @sql = @sql + 'and field 3 =' + cast (@ p3 as varchar (100))

exec (@sql)

小老鹰

Basic syntax of dynamic SQL statements
1: ordinary SQL statements can be executed with Exec

eg: Select * from tableName
         Exec ('select * from tableName')
         Exec sp_executesql N'select * from tableName '-Please be sure to add N before the string

2: When field names, table names, database names, etc. are used as variables, dynamic SQL must be used

eg:
declare @fname varchar (20)
set @fname = 'FiledName'
Select @fname from tableName-Error, no error will be prompted, but the result is a fixed value FiledName, which is not what you want.
Exec ('select' + @fname + 'from tableName')-please note that there is a space around the single quote before and after the plus sign

Of course, you can change the string into a variable.
declare @fname varchar (20)
set @fname = 'FiledName'-set field name

declare @s varchar (1000)
set @s = 'select' + @fname + 'from tableName'
Exec (@s)-success
exec sp_executesql @s-this sentence will report an error



declare @s Nvarchar (1000)-note that this is changed to nvarchar (1000)
set @s = 'select' + @fname + 'from tableName'
Exec (@s)-success
exec sp_executesql @s-this sentence is correct

3. Output parameters
declare @num int,
        @sqls nvarchar (4000)
set @ sqls = 'select count (*) from tableName'
exec (@sqls)
--How to put the result of exec execution into a variable?

declare @num int,
               @sqls nvarchar (4000)
set @ sqls = 'select @ a = count (*) from tableName'
exec sp_executesql @ sqls, N '@ a int output', @ num output
select @num

caboom04

A little dizzy, and I really can't figure out how to write the structure of my statement

caboom04

Again, thank you, the older brother upstairs, I have a headache

muxiaoke

Top it, look at it!

caboom04

Please master it