Stored Procedure Example

From: Louis Frolio <frolio_at_home.com>
Date: 2000/03/03
Message-ID: <mmEv4.20123$MZ2.309793@news1.wwck1.ri.home.com>#1/1

Greetings All, I have been a SQL DBA for some time now. However, and thankfully, my company is
moving to Oracle8i on Solaris. My current SQL server system has many stored procedures that have to
be converted to Oracle PL/SQL. I have not had a problem with the simple procedures, however, in some
of my procedures I do error trapping and I don't know if Oracle handles errors the same way. Below is
some code of a very trivial procedure written for SQL Server, I would appreciate it if someone out there
familiar with T-SQL and Microsoft PL/SQL could convert it to an Oracle stored procedure. I appreciate
any help that any of you might be able provide.

Regards, Louis
frolio_at_home.com

  CREATE PROCEDURE spAddUser

@inUserId INTEGER = NOT NULL,
@vcFirstName VARCHAR(35) = NULL,
@vcLastName VARCHAR(35) = NULL,
@vcErrorMessage VARCHAR(255) = 'NO ERROR.' OUTPUT
  AS

  SET @vcErrorMessage = 'NO ERROR.'

  IF (@inUserId IS NULL)
   BEGIN

     SET @vcErrorMessage = 'You must pass a valid UserId.'
     /* exit the procedure immediately */
     RETURN (-1)

   END   INSERT INTO tbUser
  VALUES (@inUserId, @vcFirstName, @vcLastName)

   IF (@@ERROR <> 0)
    BEGIN

      SET @vcErrorMessage = 'spAddUser, Pos 1, [Error Inserting Record].'
      /* exit the procedure immediately */
      RETURN(-1)

    END   /* exit the procedure gracefully */
  RETURN(0)   /* Grant Permisstions */
  GRANT EXECUTE ON spAddUser TO WebUser
  GO Received on Fri Mar 03 2000 - 00:00:00 CST