Select result from stored procedures in MS SQL

Hi!
I've read that I can use stored procedures to speed up my
application. I made stored procedure like this:

create procedure MySelectProc @aDog int as
select * from Dogs where HeadID=@aDog
go

I placed TStoredProc on my TDataModule, set up properties, loaded all fields in the fields editor. When I do

MyExec..ParamByName('aDog').AsInteger := aDog;
MyExec.Open;

I don't recieve any set. MyExec is empty. ISQL/W gives to me result what I want

exec MySelectProc @aDog=10

Why I can't get my stored procedure result set.

(Delphi 3.0, MS SQL 6.5)
hansmtbankAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ronit051397Commented:
As far as I know, in Stored procedures you have to create a temporary table and put the results in there.
0
hansmtbankAuthor Commented:
How do I do this?
Is this only one way to get results? It may be slow, and I can use  TQuery in my application.

TStoredProc + temporary table are slower than TQuery?
0
ronit051397Commented:
1. For SQL Servers use only TStoredProc and TQuery not TTable, recommanded by me    and Borland.
2. TQuery is faster and more reliable then TTable when it deals with SQL Servers and             large tables.
3. Use TQueries when you are doing simple and light 'Selectes'.
4. Use Stored procedures for heavy actions such as reports.
    After you genereted a result set in the stored procedure, do the following:
    - Create a table, SQL command: CREATE TABLE "SomeName"....
    - Fill that table with the results.
    - Put a TQuery and write in the sql:select * from "SomeName"...
    - If you want it to be temporary, then at some stage call the SQL command:
      DROP TABLE "SomeName"....
Don't write a Stored Procedure when you need to do a simple select, use TQuery instead. Use Stored procedures only when you need to write complex SQL programs.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.