Solved

Need examples of getting a recordset using stored procedure

Posted on 2001-08-21
5
151 Views
Last Modified: 2010-04-06

Any examples of Delphi code getting a recordset back from an SQL Server stored procedure (that needs parameters) would be greatly appreciated. I have problems with this that I am sure I can solve if I can just find some working examples. Any help would be appreciated

Kelly.
0
Comment
Question by:kfrendo
5 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6410247
just use the open-method instead of execute
0
 

Author Comment

by:kfrendo
ID: 6410306
Thanks kretzschmar but that really doesn't help at all. I specifically need examples in Delphi code so that I can figure out what I am doing wrong!
0
 
LVL 1

Accepted Solution

by:
Phoenix_s earned 25 total points
ID: 6410387
as far as I know, stored procedures do not like to return more than 1 'result row'... I could be wrong, but all the SP I have coded into the database I mess with only return 1 result row.

the proc below is for M$ SQL server 6.5, it takes 3 parameters in and returns 1
<code start------------------------------------------------
create proc sp_getmondaycount (@weekno int, @offid int, @prodid int, @pcount int output) as

select @pcount = isnull(ic.runtotal,0)
from inventorycountb ic, weeksb ww
where ww.week = @weekno
and ww.year = 98
and ic.warehouseid = @offid
and ic.productid = @prodid
and ic.rundate >= dateadd(day,1,ww.startdate)
and ic.rundate <= dateadd(day,2,ww.startdate)
<code end--------------------------------------------------

the best way to get a result set from a stored procedure is to do one of the following.

1)  if you're looking for a dataset to be returned based on parameters sent to the stored procedure, you might try using a query command and open the query, not execsql.  Then you can walk through the result with first, next, last, etc..  The following SQL QUERY command will load a query component with all the data it needs to walk through as part of a process..  startdate and enddate are parameters.
<code start------------------------------------------------
select cs.creditsummarynumber, cs.datecreated, cr.credittypeid, cr.creditnumber, ci.productid, ci.quantity


from creditsummaryb cs, creditsummaryitemb csi, creditproductb cr, credititemb ci

where csi.creditsummarynumber = cs.creditsummarynumber
and cr.creditnumber = csi.docnumber
and ci.creditnumber = cr.creditnumber
and cs.cancelled = 0
and cs.datecreated >= :startdate
and cs.datecreated < dateadd(dd,1,:enddate)
<code end--------------------------------------------------


2)  have the stored procedure insert the result into a temp table.  you will need a query or a table to walk through the result set produced by the procedure.  The following wipes the output table and then loads it with date based on a start date and end date.  the result set is held in the output table until cleared, which means it can be used by another stored procedure as input data

<code start------------------------------------------------
create proc do_compcredits_1 (@startdate datetime, @enddate datetime) as

truncate table compcreds_1

insert into compcreds_1
select cs.creditsummarynumber, cs.datecreated, cr.credittypeid, cr.creditnumber, ci.productid, ci.quantity


from creditsummaryb cs, creditsummaryitemb csi, creditproductb cr, credititemb ci

where csi.creditsummarynumber = cs.creditsummarynumber
and cr.creditnumber = csi.docnumber
and ci.creditnumber = cr.creditnumber
and cs.cancelled = 0
and cs.datecreated >= @startdate
and cs.datecreated < dateadd(dd,1,@enddate)
<code end--------------------------------------------------

to look at the result set, hook a table or a query to the compcreds_1 result table and handle the data from there.


hope this is useful.
0
 
LVL 27

Assisted Solution

by:kretzschmar
kretzschmar earned 25 total points
ID: 6410529
it depends on the database, and
if the database stored procedure can return a db-cursor

just a sample,
in ms-access u can define a crosstab-query,
you can access this database-object in delphi with a stored-procedure and
retrieve the rows with the open-method and iterating with the next method

(well access isn't a real database, but the point is in the heading of this comment)

meikl ;-)
0
 
LVL 17

Expert Comment

by:geobul
ID: 9287702
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

split points between Phoenix_s and kretzschmar

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Thanks,

geobul
EE Cleanup Volunteer
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now