stored procedure

[jeny]

hi,

i am trying to implement stored procedure. and here is the procedure

 

CREATE PROCEDURE getData(

  IN in_user VARCHAR(25),

  OUT out_fname VARCHAR(25)

)

 

BEGIN

  SELECT fname

  FROM organization.customers

  WHERE username = in_user

  INTO out_fname

END;

 

i am trying to run this is as SQL statement. it is not running. can you people help me out?

[Mark Baker]

i am trying to run this is as SQL statement. it is not running. can you people help me out?

If you want to return the data via a standard SQL statement (e.g. a SELECT), then you might be better creating a function rathe rthan a procedure

 

CREATE FUNCTION getData(
   in_user IN VARCHAR2(25)
)
   RETURN VARCHAR2(25)
IS
   out_fname VARCHAR2(25);
BEGIN
   SELECT fname
     INTO out_fname
     FROM organization.customers
    WHERE username = in_user;

   RETURN out_fname;
END;

 

If you're using Oracle (as I assume you are), you can then call this using:

SELECT getData('Hello World')
  FROM DUAL;

 

Of course, you also need some form of error trapping in case a username of "Hello World" doesn't exist in your customers table.

 

 

[jeny]

i tried to run this statement

 

CREATE FUNCTION getData(

  in_user IN VARCHAR(25)

)

  RETURN VARCHAR(25)

IS

  out_fname VARCHAR(25);

BEGIN

  SELECT fname

    INTO out_fname

    FROM organization.customers

    WHERE username = in_user;

 

  RETURN out_fname;

END;

 

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN VARCHAR(25)

)

  RETURN VARCHAR(25)

IS

  out_fname VARCHAR(25)' at line 2

 

i remove the varchar also and check it is not running.