At 02:08 AM 6/12/2003 +0000, you wrote:

>Can somebody please help me with stored procedure, i try make
>procedure that make select action, i get error from FirebirdSql on
>procedure compilation, is there special way do procedures that make
>select action ? If yes, how i do it ?
>I actualy want return all data to DataSet (i make project on ASP.NET
>C#, and then i check it there.
>
>here is the procedure code i ty use:
>
>CREATE PROCEDURE IF_USER_EXIST (
> LOGINNAME VARCHAR(20))
>AS
>begin
> SELECT * FROM fo_users WHERE us_name = :LOGINNAME ;
> suspend;
>end

1. You have to SELECT...INTO :output variable (you have no output variables)
2. Select * is not valid when you have only one variable defined

From your description it is not clear what you want to do.

For an existence test:

CREATE PROCEDURE IF_USER_EXIST (
LOGINNAME VARCHAR(20)
RETURNS(TRUEFALSE SMALLINT)
AS
begin
TRUEFALSE = 0;
IF (EXISTS(
SELECT 1 FROM fo_users WHERE us_name = :LOGINNAME))
THEN
TRUEFALSE = 1;
suspend;
end

To return a multi-row dataset:

CREATE PROCEDURE IF_USER_EXIST (
LOGINNAME VARCHAR(20)
RETURNS(FIELD1 INTEGER,
FIELD2, ....
FIELDx SOMETYPE)
AS
begin

FOR
SELECT field1, field2, ....., fieldx
FROM fo_users WHERE us_name = :LOGINNAME
INTO :FIELD1, :FIELD2, ....:FIELDx
DO
suspend;
end

helen