• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

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.  
0
anthonyfaunt
Asked:
anthonyfaunt
1 Solution
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
anthonyfauntAuthor Commented:
Thanks kubeerja - I am most appreciative
Tony
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now