Solved

Need examples of getting a recordset using stored procedure

Posted on 2001-08-21
5
152 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

861 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

24 Experts available now in Live!

Get 1:1 Help Now