Subject RE: [IBO] TIB_StoredProc ISC ERROR MESSAGE - multiple rows in singleton select-
Author Alan McDonald
> SET TERM ^^ ;
> CREATE PROCEDURE P_LOGPAS_SEARCH (
> R_CREATED_BY_IN VarChar(255),
> R_SHARED_IN SmallInt)
> returns (
> R_ID Integer,
> R_CREATED_BY VarChar(255),
> R_LOGIN VarChar(255),
> R_PASSWORD VarChar(255),
> R_URL VarChar(255),
> R_CREATED Date,
> R_MODIFIED Date,
> R_SHARED SmallInt,
> R_PRIMARY VarChar(32),
> R_SECONDARY VarChar(32),
> R_COMMENTS VarChar(1024),
> R_FILE_NAME VarChar(255))
> AS
> begin
> if( :R_SHARED_IN = 0 )then
> begin
> SELECT R_ID,
> R_CREATED_BY,
> R_LOGIN,
> R_PASSWORD,
> R_URL,
> R_CREATED,
> R_MODIFIED,
> R_SHARED,
> R_PRIMARY,
> R_SECONDARY,
> R_COMMENTS,
> R_FILE_NAME
> FROM LOGPAS
> WHERE ((R_CREATED_BY = :R_CREATED_BY_IN) AND ( R_SHARED =
> :R_SHARED_IN))
> INTO
> :R_ID,
> :R_CREATED_BY,
> :R_LOGIN,
> :R_PASSWORD,
> :R_URL,
> :R_CREATED,
> :R_MODIFIED,
> :R_SHARED,
> :R_PRIMARY,
> :R_SECONDARY,
> :R_COMMENTS,
> :R_FILE_NAME;
> end
> else
> if( :R_SHARED_IN = 1 )then
> begin
> SELECT
> R_ID,
> R_CREATED_BY,
> R_LOGIN,
> R_PASSWORD,
> R_URL,
> R_CREATED,
> R_MODIFIED,
> R_SHARED,
> R_PRIMARY,
> R_SECONDARY,
> R_COMMENTS,
> R_FILE_NAME
> FROM LOGPAS
> WHERE ((R_CREATED_BY = :R_CREATED_BY_IN) OR ( R_SHARED =
> :R_SHARED_IN))
> INTO
> :R_ID,
> :R_CREATED_BY,
> :R_LOGIN,
> :R_PASSWORD,
> :R_URL,
> :R_CREATED,
> :R_MODIFIED,
> :R_SHARED,
> :R_PRIMARY,
> :R_SECONDARY,
> :R_COMMENTS,
> :R_FILE_NAME;
> end
> end
> ^^
> SET TERM ; ^^
>
> Hope that you may spot the problem
>
> danieL

When you select INTO variables, it's for the purpose of further processing
or SUSPENDING the results back as a selectable stored procedure.
You are selecting INTO but the WHERE clause is not creating a singelton
select.
You can't select multiple values into the variables.
You would eed to do a FOR SELECT loop to select INTO the variables and
SUSPEND each loop to return those variables or process each record.
Alan