Passing a Wildcard to a Parameter

Transact-SQL
1

Using the following SQL in VB passing to Crystal Reports

> DECLARE @Year int =  '{?ADate}'
> DECLARE @Broker NVARCHAR(50) = '{?Broker}'
> 
> SELECT l.PETS_LOAD_NUMBER AS 'LOAD NUMBER',
          SUM(l.LINEHAUL_PAID +
              l.FUEL_SUR_PAID + 
              l.RFR_FUEL_SUR_PAID) AS 'REVENUE', 
          MAX(l.AGENT_FEE) AS 'BROKER FEE', 
          MAX(l.BROKER_AGENCY) AS 'BROKER'
>        FROM Load_Info_Table AS l
>       WHERE l.PETS_LOAD_NUMBER LIKE CONCAT(@Year, '%') 
          AND l.BROKER_AGENCY = '@Broker'
>    GROUP BY l.PETS_LOAD_NUMBER
>    ORDER BY l.PETS_LOAD_NUMBER

I need to be able to pass any one Broker (chosen from a drop down list within the program) or by choosing ALL Brokers (prefer it to be the default value). I cannot determine how to pass a variable that would equal a wildcard to the @Broker parameter. Is this possible?

The query works great if I pass a specific Broker. I cannot figure out how to pass a wildcard of sorts to the parameter to allow all brokers or really all records to be shown.

2

Add or

OR @broker = -1

Or

OR @broker = 'ALL Brokers'

Is there a difference between the display value and the drop down value?

Display, value
Broker , 1
ALL Brokers, -1

Or better yet use stored procedures

1 Like
3

I think I came up with something that works or at least appears to. If there is a better option, please advise:

DECLARE @Year int =  '{?Adate}'
DECLARE @BROKER NVARCHAR(50) = '{?Broker}'

If @BROKER = 'ALL'

Begin
	SELECT l.PETS_LOAD_NUMBER AS 'LOAD NUMBER', SUM(l.LINEHAUL_PAID + l.FUEL_SUR_PAID + l.RFR_FUEL_SUR_PAID) AS 'REVENUE', MAX(l.AGENT_FEE) AS 'BROKER FEE', MAX(l.BROKER_AGENCY) AS 'BROKER'
      FROM Load_Info_Table AS l
     WHERE l.PETS_LOAD_NUMBER LIKE CONCAT(@Year, '%')
  GROUP BY l.PETS_LOAD_NUMBER
  ORDER BY l.PETS_LOAD_NUMBER
End

Else

If @BROKER <> 'ALL'

Begin
    SELECT l.PETS_LOAD_NUMBER AS 'LOAD NUMBER', SUM(l.LINEHAUL_PAID + l.FUEL_SUR_PAID + l.RFR_FUEL_SUR_PAID) AS 'REVENUE', MAX(l.AGENT_FEE) AS 'BROKER FEE', MAX(l.BROKER_AGENCY) AS 'BROKER'
      FROM Load_Info_Table AS l
     WHERE l.PETS_LOAD_NUMBER LIKE CONCAT(@Year, '%') AND l.BROKER_AGENCY = @BROKER
  GROUP BY l.PETS_LOAD_NUMBER
  ORDER BY l.PETS_LOAD_NUMBER
End
4

That should work as well.