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.


Thanks n Regards,
==============
Rambabu Piridi.
Software Engineer,
Wipro Technologies,
Madhapur,
Hyderabad.

e-Mail : rambabu.piridi@... <mailto:rambabu.piridi@...>
Mobile: 99491 83303.
==================


________________________________

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Daniel Albuschat
Sent: Tuesday, July 25, 2006 8:08 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Badly need your Help-Very urgent



2006/7/25, Graeme Edwards <g.edwards@...
<mailto:g.edwards%40sportingpulse.com> >:

> My guess would be more along the lines of
>
> Select * from user left outer join userproperties on . where

user.userid in

> (Select first 21 skip 19 userID from User)

Ah, now I see what he means.
Hm that's indeed not a simple problem. I think you can gain the fastest
results when you write a stored procedure that selects the wanted
rows from user and then join this stored proc to userproperties.

set term ^;
create procedure select_users(i_first integer, i_skip)returns(userid
integer, username varchar(10) /* or whatever */) as
begin
for select first :i_first skip :i_skip userid, username from user
into :userid, :username do
begin
suspend;
end
end^

select * from select_users(21,19) u left join userproperties p .....

The code is untested, it's not guaranteed to work. But you should get
what I mean.
I don't know whether it's ok to give the argiments to first and skip
as parameters....
probably it's not.

Regards,
Daniel Albuschat

--
eat(this); // delicious suicide





[Non-text portions of this message have been removed]