D5 Stored Proc into MSSQL7 with Result Set

I am using a Query with a complex SQL script.  I want to simplify the SQL script by the use of DECLARED variables to avoid repetitive uses of the same block of SQL code (a SUM call which may or may not be NULL).  I have not worked out how to return a result set from a stored procedure and would be very appreciative to get some demo code from someone who has worked out how to do this.  
anthonyfauntAsked:
Who is Participating?
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.

dhnkleyCommented:
Listening....
0
intheCommented:
hello,
i dunno much about this stuff so im guessing a bit but this was from previous question:

Q:
How to write StoredProc (produced a result set) and use it in Delphi?
 
A:
First, create a package:
 
CREATE PACKAGE MYPKG
IS
  TYPE CursorType IS REF CURSOR;
END MyPkg;
 
Then the procedure:
 
CREATE PROCEDURE RETURN_RESULT_SET
(oCursor IN OUT MyPkg.CursorType) AS
BEGIN
  open oCursor for select * from SomeTable;
END;  
Then in Delphi:
1. Set the TStoredProc parameter as type Cursor.
2. At this point, treat the TStoredProc component like a TQuery.     (Use the Open method instead of ExecProc.)

the below question is also talking about sql and stored procs so maybe some info there that helps:
http://www.experts-exchange.com/jsp/qShow.jsp?ta=delphi&qid=10264027 
Regards Barry
0
intheCommented:
dhnkley,
maybe you wanna withdraw your proposed answer, and make it a comment ...
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

rwilson032697Commented:
Don't you *ever* sleep Barry? :-)
0
anthonyfauntAuthor Commented:
dhnkley intended to add a 'listening' comment but has added 'lestening' as an answer
0
anthonyfauntAuthor Commented:
Barry,
Thanks for your advice.  I have not used packages and tried executing your script:

CREATE PACKAGE MYPKG
IS
  TYPE CursorType IS REF CURSOR;
END MyPkg;

in Query Analyzer but it did not like it.

I then looked into creating a package in Enterprise Manager / Data Transformation Services but there are too many options.
Have increased the point because my question is obviously more difficult than I thought and would be grateful if you could give further advice.
regards,
Tony

   
0
intheCommented:
raymond ,
erm  no.. well not enough anyway  ;-)

Tony,
wish i could help more but i really dont know what im talking about (the above code was for oracle is all i know)i let someone who knows this stuff reply so i dont fill this thread any more with rubbish :-)
0
kubeerjaCommented:
to use a storedproc to return a record set just treat it like a TQuery , First pass the input parameters then use storedproc1.open instead of execProc as in the following example :

you can create this in the pubs database:

CREATE PROCEDURE store_sale  @sid int AS
select * from sales where stor_id=@sid

and in Delphi :

procedure TForm1.Button1Click(Sender: TObject);
begin
 with StoredProc1 do
 begin
   Prepare ;
   ParamByName('@sid').AsInteger :=7131 ;
   Open ;
 end;
end;
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
anthonyfauntAuthor Commented:
Thanks kubeerja - I am most appreciative
Tony
0
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.