At 04:11 PM 26/07/2006, you wrote:

>
>
>
>I am trying to create a stored procedure with the following
>information , But it is showing exception at first key word.
>
>Here is the structute
>
> CREATE PROCEDURE GetUserInfo(i_first integer, i_skip integer)
> RETURNS (usr_id VARCHAR(80), usr_name VARCHAR(80), usr_pwd
>VARCHAR(80), role_id VARCHAR(1), usr_code VARCHAR(20), email_id VARCHAR
>256), description VARCHAR(300)
> )
> AS
> BEGIN
> for select first:i_first, skip :i_skip usr_id, usr_name, usr_pwd,
>role_id, usr_code, email_id, description
> FROM usr_basic_info
> INTO :usr_id, :usr_name, :usr_pwd, :role_id, :usr_code, :email_id,
>:description
> DO
> BEGIN
> SUSPEND;
> END
> END
>
>ERROR
>======
> 10:46:50 [CREATE - 0 row(s), 0.000 secs] [Error Code: 335544569, SQL
>State: HY000] GDS Exception. 335544569. Dynamic SQL Error
>SQL error code = -104
>Token unknown - line 7, char 18
>:
>... 1 statement(s) executed, 0 row(s) affected, execution time 0.000 sec
>
>Can we use FIRST/SKIP inside a StoredProcedure.

It is STUPID to use FIRST/SKIP inside a SP, since you can just run
through the set and output whatever rows you like, with 1000% more
efficiency. So I won't even bother to analyse your various syntax
errors.....FIRST/SKIP is a rather dopey invention mimicking what
people have to do in database engines that don't support outputting
sets directly from cursor operations.

Do something like this:

CREATE PROCEDURE GetUserInfo(i_first integer, i_skip integer)
RETURNS (usr_id VARCHAR(80), usr_name VARCHAR(80), usr_pwd
VARCHAR(80), role_id VARCHAR(1), usr_code VARCHAR(20), email_id VARCHAR
256), description VARCHAR(300)
)
AS
declare current_count integer = 0;
declare output_count integer = 0;
BEGIN
for select
usr_id, usr_name, usr_pwd,
role_id, usr_code, email_id, description
FROM usr_basic_info
INTO :usr_id, :usr_name, :usr_pwd, :role_id, :usr_code, :email_id,
:description
DO
BEGIN
current_count = current_count + 1;
if (current_count > i_skip) then
begin
if (output_count >= i_first) then
Exit;
else
SUSPEND;
current_output = current_output + 1;
end
END
END

./heLen