Try this version

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 (dr_id, cc_user) values ( :str_dr_id,
:int_user_id );
end

-----Original Message-----
From: akashelkar [mailto:akashelkar@...]
Sent: 22 July 2003 15:21
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Writing A Stored Procedure ??


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?


--- In firebird-support@yahoogroups.com, Paul Vinkenoog <paul@v...>
wrote:

> > > suspend;
> > > end
>
> Oh, another thing: you only use SUSPEND if you want to return values
> to the caller; and even then, only in a SELECTABLE procedure. You
> didn't show the proc header but from the body it didn't look like

that

> kind of SP. (I can imagine that you want to return a status code to
> the caller here, e.g. to indicate whether the record already existed
> or if you had to insert one, but you wouldn't return multiple rows

so

> an executable SP - without suspend - looks more logical.)
>
> Grtz,
> Paul

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/