Solved

Need examples of getting a recordset using stored procedure

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Syntax Check Delphi Seattle IOS app without MAC ? 1 104
Working with hours 3 65
Dev Express grid collapse 2 45
Reconfigure Delphi Install? 2 60
A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

840 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