|Subject||Re: [firebird-support] SP Creation|
> Hello all,almost
> I've not used SP's much, but with my current project, I want to make
> exclusive use of them. Anyway, I am having a bit of trouble with mycurrent
> SP. I keep getting a "Variable CUSTOMERPASS unknown". If I compileanyway,
> it goes through and subsequent changes to the SP do not produce thaterror.
> I am using IBExpert personal so I wan to see if its something with it ormy
> lack of understanding. Also this is the first time I've used a Dialect 3I assume that CUSTOMERPASS is a field of Customers? Did you use quotes to
> database, but do not quote out anything in DDL statements.
> SET TERM ^;
> CREATE PROCEDURE SP_VALIDATECUSTOMER (
> IP_CUSTOMERID INTEGER,
> IP_CUSTOMERPASS VARCHAR(35))
> RETURNS (
> OP_VALIDATED INTEGER)
> DECLARE VARIABLE V_CUSTOMERPASS VARCHAR(35);
> SELECT CUSTOMERPASS /* <=== Here is offending portion */ FROM Customers
generate the DDL for the tables? If so, it would be case-sensitive.
> WHERE CustomerID = :IP_CustomerIDINTO :v_CustomerPass;
> INTO v_CustomerPass;
Always use the prefix colon ( : ) when referencing variables in an SQL
statement for any procedure. The colon does not need to be used for
statements outside of SQL context such as the following IF statement below.
> IF (:IP_CustomerPass = :v_CustomerPass) ThenHTH
> OP_Validated = 1;
> OP_Validated = 0;
> SET TERM ; ^