Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

Need examples of getting a recordset using stored procedure


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
kfrendo
Asked:
kfrendo
2 Solutions
 
kretzschmarCommented:
just use the open-method instead of execute
0
 
kfrendoAuthor Commented:
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
 
Phoenix_sCommented:
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
 
kretzschmarCommented:
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
 
geobulCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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