I in what's personal make a select before the first insert to verify that
the user does not exist already.

select a.username from members a where a.username = :username into
:group_name;
if (row_count = 0) then
begin
........
end

Regards
=========
|| ISMAEL ||
=========

----- Original Message -----
From: "Luiz Henrique Martino" <luiz@...>
To: <firebird-support@yahoogroups.com>
Sent: Thursday, June 10, 2010 4:18 PM
Subject: Re: [firebird-support] MS SQL Server stored procedure to Firebird


> Something like this:
>
> CREATE PROCEDURE INSERT_MEMBER
> (
> USERNAME VARCHAR(25),
> PASSWORD VARCHAR(25)
> )returns (
> GROUPNAME varchar(25),
> LASTMODIFIED date
> )
> AS
> DECLARE VARIABLE GROUP_NAME VARCHAR(25);
> BEGIN
>
> INSERT INTO MEMBERS (USERNAME, PASSWORD) VALUES (:USERNAME, :PASSWORD);
>
> SELECT FIRST 1 GROUP_NAME FROM GROUPS ORDER BY LAST_MODIFIED INTO
> :GROUP_NAME;
>
> INSERT INTO GROUP_MEMBERS (GROUP_NAME, USERNAME) VALUES (:GROUP_NAME,
> :USERNAME);
>
> FOR
> SELECT FIRST 100 GROUP_NAME, LAST_MODIFIED FROM GROUPS INTO :GROUPNAME,
> :LASTMODIFIED
> DO BEGIN
> suspend;
> END
>
> END
>
>
>
>
> []'s
> Martino
>
> 2010/6/10 Luiz Henrique Martino <luiz@...>
>
>> In Firebird, you need to declare a variable for each column that you
>> pretend to return in your procedure. Please, change the "SELECT TOP(100)
>> *
>> FROM groups" by another specifying your column names.
>>
>> Sometimes, this is boring, but on the other hand, avoids that you
>> increase
>> the unnecessary data traffic on network (I think)
>>
>> []'s
>> Martino
>>
>> 2010/6/10 sqlsvr <sqlsvr@...>
>>
>>
>>>
>>> I'm migrating from SQL server to firebird, how do you convert the
>>> following MS SQL stored procedure to firebird?
>>>
>>> CREATE STORED PROCEDURE insert_member
>>> (
>>> @username VARCHAR(25),
>>> @password VARCHAR(25)
>>> )
>>> AS
>>> BEGIN
>>> BEGIN TRANSACTION
>>> DECLARE @group_name VARCHAR(25);
>>> INSERT INTO members (username, password) VALUES (@username, @password);
>>> SELECT TOP 1 @group_name = group_name FROM groups ORDER BY
>>> last_modified;
>>> INSERT INTO group_members (group_name, username) VALUES (@group_name,
>>> @username);
>>>
>>> SELECT TOP(100) * FROM groups;
>>> COMMIT;
>>> END
>>>
>>>
>>>
>>
>>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>