SQL stored procedure to check value yes/no in a table and execute sql

Question

Check if value=Y in table1 and then execute immediate sql_select

if
(select value1 from table1 where value_desc='Indicator' and value1='Y')
then 
    execute immediate sql_select_yes
else 
    execute immediate sql_select_no

Answer 1

There is no if (cursor) construction or indeed any exists operator in PL/SQL syntax. You will need to do something like this:

declare
    somevar number;
begin
    select count(*) into somevar
    from   table1
    where  value_desc = 'Indicator'
    and    value1 = 'Y'
    and    rownum = 1;

    if somevar > 0 then
        execute immediate sql_select_yes
    else 
        execute immediate sql_select_no
    end;
end;

The and rownum = 1 condition is just in case there are a large number of rows, as you don't need it to count all of them for an existence test. (It won't affect the result if it has to count a million rows, it's just a waste of time when you only care if one row exists.) You could equally use something like this for the existence check:

select count(*) into somevar from dual
where  exists
       ( select 1
         from   table1
         where  value_desc = 'Indicator'
         and    value1 = 'Y'
         and    rownum = 1 );

Answer 2

You may refer this SO answer to call other SP

And refer this link for correct usage of Select - EXISTS (seems IF and EXISTS is not a valid combination)