Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need examples of getting a recordset using stored procedure

Posted on 2001-08-21
5
Medium Priority
?
162 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 100 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 100 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview

824 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