At 02:21 PM 22/07/2003 +0000, you wrote:

>OK, first of all many thanks to Alex and Paul both of you :)
>
>I tried the following now:
>
>---------- 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" );
> suspend;
>end
>
>---------- ERROR MESSAGE ----------
>
>Invalid token.
>Dynamic SQL Error.
>SQL error code = -104.
>Token unknown - line 10, char 17.
>
>****************
>
>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.
>
>
>Where am I going wrong?? The CC_USERS table is definitely present in
>the database. So how should I write the INSERT statement?

1. The first one should almost work if you take away the square braces and
fix up the usage of case-sensitive identifiers.
2. The second one doesn't work, primarily because you didn't prefix the
variables with colons. Case-sensitivity errors here too.
3. Don't include the SUSPEND statement. It's wrong syntax for DML.
4. If you have more columns in cc_users than just the two you are writing
to, then you will need to specify a column list for cc_users.
5. It's probably just careless typing, but if you insist on declaring
variables inside quoted identifiers then you make the identifiers
case-sensitive and you need to use the quotes when referring to them.
6. Read the Language Reference.

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 (Colx, Coly)
values ( :"str_DR_ID", :"int_USER_ID" );
end

heLen