Skip to content
Advertisement

How to use different conditions depending on variable value. Pro C / SQL

I am quite new to pro c and wanted to know wether the below is possible.

char car[11];
strcpy(car,""); /* default value*/

if this variable has a value it should be used within the sql below : So run for given car value only.

select *
from vehicles
where car = :car;

However if car is null then do the following sql: So run for all cars.

select *
from vehicles;

Is there a way to merge these two without needing to create duplicate sql queries where ones searches for a given car value and another for all cars.

Any Help would be appreciated.

Advertisement

Answer

As of SQL, that would be

select *
from vehicles
where (car = :car or :car is null)

Example from SQL*Plus (don’t worry about &&car substitution variable; you’ll use :car):

Passing variable value:

SQL> with vehicles (car) as
  2    (select 'BMW'      from dual union all
  3     select 'Mercedes' from dual union all
  4     select 'Audi'     from dual
  5    )
  6  select *
  7  from vehicles
  8  where (car = '&&car' or '&&car' is null);
Enter value for car: Audi

CAR
--------
Audi

No value:

SQL> undefine car
SQL> /
Enter value for car:
                                --> here I pushed ENTER, without entering a value
CAR
--------
BMW
Mercedes
Audi

SQL>
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement