Retrieve data using Stored Procedure

Can anyone post me an example of a Stored Procedure that returns me all the data from a table using a "Select". I have a table called "COMPANY" that has the fields "CODE, NAME, LOCAL, UF". How would my Stored Procedure be to retrieve all of the Companies and show in my DataGrid ?? Thanx

An example right from the docs (www.ibphoenix.com

But the Stored Procedure that I need will need to retrieve more than one register. It would be like doing:

SELECT CODE, NAME, UF FROM COMPANY


And that sql will return me more than one register. How would my stored procedure be ???

I have found small error in pabloj's example ;-)

Correct is something like:

CREATE PROCEDURE proc_name
RETURNS (
ID INTEGER,
SDATA INTEGER,
TXT VARCHAR(10))

AS
BEGIN
FOR SELECT ID,
SDATA,
TXT
FROM Table_name
INTO :ID,
:SDATA,
:TXT
DO
BEGIN
SUSPEND;
END
END

I am here for more questions ;-)

--
Best regards,
Fikret Hasovic http://fikret.fbtalk.net
USAID TAMP Senior Programmer
* Firebird Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation
* JEDI VCS contributor
http://jedivcs.sourceforge.net/
* Firebird and Fyracle news
http://www.fyracle.org

It still doesn´t do what I need. Let me try this way. How do I put this query as a Stored Procedure ? "SELECT CODE, NAME, STATE FROM COMPANY"

In which will return me more than one rows.

Define code, name, state as output parameters and

...

FOR SELECT CODE, NAME, STATE FROM COMPANY INTO :CODE, :NAME, :STATE DO
SUSPEND;

...

CREATE PROCEDURE return_company_data
RETURNS (
CODE INTEGER,
NAME VARCHAR(10),
STATE VARCHAR(10))

AS
BEGIN
FOR
SELECT CODE, NAME, STATE
FROM COMPANY
INTO :CODE, :NAME, :STATE
DO
SUSPEND;
END

Just set correct data types here.


--
Best regards,
Fikret Hasovic http://fikret.fbtalk.net
USAID TAMP Senior Programmer

* Firebird Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation
* JEDI VCS contributor
http://jedivcs.sourceforge.net/
* Firebird and Fyracle news
http://www.fyracle.org

Thanks...
Just two more question. If I want to use filters in the select. Can I do something declare a parameter "prmCODE" and so something like:

If (prmCODE <> '') then
where CODE = prmCODE

I want to use this Stored Procedure to retreive Data from my table and show it in my DataGrid. Would this increase the performance of my ASP .NET and Windows Application ??? Instead of passing pure queries ?? Thanks

Yes, it can be done.
But it should be something like:
If (prmCODE <> '') then
where CODE = :"prmCODE"



Probably it will increase performance. But it all depends...


--
Best regards,
Fikret Hasovic http://fikret.fbtalk.net
USAID TAMP Senior Programmer

* Firebird Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation
* JEDI VCS contributor
http://jedivcs.sourceforge.net/
* Firebird and Fyracle news
http://www.fyracle.org

Why I see that Freedyprogram is the person last who posted here?


--
Best regards,
Fikret Hasovic http://fikret.fbtalk.net
USAID TAMP Senior Programmer

* Firebird Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation
* JEDI VCS contributor
http://jedivcs.sourceforge.net/
* Firebird and Fyracle news
http://www.fyracle.org

how about



as you can
EXECUTE PROCEDURE GETDATA ( '%TEXT%' );
EXECUTE PROCEDURE GETDATA ( '' );

Flashang: if you use EXECUTE, you won't get multiple rows, but only 1.

Use SELECT instead.

When I call the select procedure, SelProc_All_Constituents with
“select * from SelProc_All_Constituents “
then use a TADOQuery.locate from my app, I receive the following error:
“Error: multiple rows in singleton select”

SelProc_All_Constituents Source

set term ^;
create procedure SelProc_All_Constituents
RETURNS (
constituentID integer,CT_Code integer,Inactive_YN varchar(3))
as
begin
select constituentID,CT_Code,Inactive_YN from constituents
into :constituentID,:CT_Code,:Inactive_YN;
suspend;
end^

This is my first FB procedure ever.
Any ideas?
Thx
Sam Hunt

You have a singleton select -> the SELECT with INTO and only do the SUSPEND once.

If you want to return a multi-row result from this procedure, make it a cursor select:

FOR SELECT ... INTO :variables ... DO SUSPEND;

Here's my latest version of my select procedure that still returns just one row when I do a "select * from selproc_all_constituents9".
============
set term ^;
create procedure SelProc_All_Constituents9
RETURNS (
constituentID integer,CT_Code integer,Inactive_YN varchar(3),Deceased_YN varchar(3),
CoupleID integer,Primary_PartnerID integer,Secondary_PartnerID integer,Display_Name varchar(30),
Business_Name varchar(30),Last_Name varchar(20),First_Name varchar(20),MI char(1),
Couples_Title varchar(30),EmployerID integer,DonorID varchar(3),Title varchar(20),Suffix varchar(30),
Mail_YN varchar(3),Sort_Name varchar(30),Soundex varchar(30),Formal_Address_Name varchar(50),
Informal_Address_Name varchar(50),Formal_Salutation varchar(50),Informal_Salutation varchar(50),
Referred_By varchar(50),Job_Title varchar(30),Business_Primary_Contact varchar(50),
Use_Employer_Address varchar(3))
as
begin
for select constituentID,CT_Code,Inactive_YN,Deceased_YN,
CoupleID,Primary_PartnerID,Secondary_PartnerID,Display_Name,
Business_Name,Last_Name,First_Name,MI,
Couples_Title,EmployerID,DonorID,Title,Suffix,
Mail_YN,Sort_Name,Soundex,Formal_Address_Name,
Informal_Address_Name,Formal_Salutation,Informal_Salutation,
Referred_By,Job_Title,Business_Primary_Contact,
Use_Employer_Address from constituents
into :constituentID,:CT_Code,:Inactive_YN,eceased_YN,:CoupleID,:Primary_PartnerID,
:Secondary_PartnerID,isplay_Name,:Business_Name,:Last_Name,:First_Name,:MI,:Couples_Title,
:EmployerID,onorID,:Title,:Suffix,:Mail_YN,:Sort_Name,:Soundex,:Formal_Address_Name,
:Informal_Address_Name,:Formal_Salutation,:Informal_Salutation,:Referred_By,:Job_Title,
:Business_Primary_Contact,:Use_Employer_Address
do suspend;
end^
============
why does this only return one row?
btw, the green smiley faces are actually the colon and uppercase D.
Thx
Sam Hunt