Hello Anand,

> CREATE PROCEDURE NEW_PROCEDURE (
> "str_DR_ID" VARCHAR(50),
> "int_USER_ID" INTEGER)
> AS
> begin
> /* Procedure Text */
> if (not exists(select * from cc_users where dr_id=:"str_DR_ID"
> and cc_user=:"int_USER_ID"))
> then
> insert into [cc_users] values ( :"str_dr_id", :"int_user_id" );
> suspend;
> end
>
> ---------- ERROR MESSAGE ----------
>
> Invalid token.
> Dynamic SQL Error.
> SQL error code = -104.
> Token unknown - line 10, char 17.

That's about the [ ] - they shouldn't be there.

> Before the above, I had tried the following:
>
> ---------- STATEMENT ----------
>
> CREATE PROCEDURE NEW_PROCEDURE (
> "str_DR_ID" VARCHAR(50),
> "int_USER_ID" INTEGER)
> AS
> begin
> /* Procedure Text */
> if (not exists(select * from cc_users where dr_id=:"str_DR_ID"
> and cc_user=:"int_USER_ID"))
> then
> insert into cc_users values ( str_dr_id, int_user_id );
> end
>
>
>
> ---------- ERROR MESSAGE ----------
>
> Column does not belong to referenced table.
> Dynamic SQL Error.
> SQL error code = -206.
> Column unknown.
> STR_DR_ID.
> At line 10, column 35.

In this case, you *must* use colons (':') before the parameter names,
because you use them as values in an insert statement. If you don't,
Firebird assumes that you are mean a column of the table you're
inserting into.

But wait... there's more: in the SP header, you declared the
parameters enclosed in double quotes: "str_DR_ID", "int_USER_ID".
This makes them case-sensitive, so you must always refer to them with
the double quotes and in the correct capitalization.

But why don't you save yourself the trouble? Unless there's a very
good reason, declare variables (and tables, and columns, etc.) without
double quotes.

And kick that &%#$&$% SUSPEND out! :-)

I think the procedure should look like this (didn't test though, might
still contain errors):


create procudure InsertIfNotThere
( str_dr_id varchar( 50 ), int_user_id integer )
as
begin
if ( not exists ( select * from cc_users
where dr_id = :str_dr_id
and cc_user = :int_user_id ) )
then
insert into cc_users values ( str_dr_id, int_user_id );
end


That ought to work (if cc_users only has the columns dr_id and
cc_user, in that order).


BTW: Have you got the Firebird Quick Start Guide and the Borland IB6
beta docs? Both freely available at www.ibphoenix.com; follow the
"Main Downloads" link. You *really* need those docs if you want to be
a Firebird Master :-)


Greetings,
Paul Vinkenoog