Re: OK... maybe I'm being stupid... HELP

First of all, the correct syntax in allocating the count to HowMany is

BEGIN
   SELECT COUNT(*)
   into HowMany
   FROM BankName
   WHERE CustomerSurname = Surname;
END; The syntax is now correct, however, it won't work since you are trying to quiey fron a table which is determined at runtime (Bankname).

Look at dbms_sql. This will enable you to build queries based on results users enter at runtime. The you can do something like

v_sql_clause := 'Select count(*) from ' || bankname || ' where customersurname = ' || surname';

You may want to use a stored function, returning HowMany as opposed to a stored procedure as well.

HTH, Mark

Nick Bull wrote in message
<931184371.2352.0.nnrp-09.d4e48d0d_at_news.demon.co.uk>...
>Hi.
>
>I might be really stupid here, but I can't seem to work out what I'm doing
>wrong.
>
>I'm trying to create a stored procedure that I can call from an external
>program. This procedure will accept two parameters and return one further
>parameter. I want to be able to pass in some value to match on, and a
value
>for the table name.
>
>e.g.
>I have a database that contains one table per bank (this is a big group of
>bank's database). I want to know how many customers that bank with Mildew
>bank have the surname Jones.
>
>CREATE OR REPLACE PROCEDURE HowManyCustomers
> ( BankName IN VARCHAR2,
> Surname IN VarChar2,
> HowMany OUT NUMBER ) IS
>BEGIN
> HowMany := SELECT COUNT(*) FROM BankName
> WHERE CustomerSurname = Surname;
>END;
>
>
>What am I doing wrong ?... I keep getting errors on this script, but I
>can't find anything that is helpful in the documentation or in the books
>I've got.
>
>Cheers,
>
>NIck.
>
>
Received on Mon Jul 05 1999 - 09:33:23 CDT