Karen That's a beauty of an example! Thanks for posting it!
David Blocker ----- Original Message ----- From: "tellef" <[EMAIL PROTECTED]> To: "All" <[EMAIL PROTECTED]> Sent: Thursday, June 13, 2002 2:21 PM Subject: Stored Procedures in reports > > Just in case this would be handy for others who haven't > tried this yet, remember how powerful it is to call a > stored procedure from within a report. > > A client needed a message printed on the bottom of a report. I was > to count up how many invoices a customer had with a bunch of > different criteria, such as date of invoice, whether paid off, if > paid off within certain time frame, amount of invoice, etc. If no > invoices met the criteria, then no message on the report. I could > have (1) calculated this outside the report in a temp table, but this > report is called from many different programs so it would be nice to > have it within the report itself, or (2) I could define a whole bunch > of complicated report variables. Instead I used a stored procedure. > > My report variables: > > INTEGER: RF : rvNoInvoices = (CALL WDeliver(CustID)) > *I am passing to the stored procedure the CustID, it will > be returning a variable with the number of invoices > TEXT : RF : rvFooter = > (IFGT(.rvNoInvoices,0,'CONGRATULATIONS! <etc....>', ' ')) > > > Here's the text of the stored procedure: > > *( WDeliver.sp, stored procedure run from report WinDelivery > Will be passed a CustID > Returns a count of invoices that meet criteria) > > -- Initialize the passed in variable > SET VAR vCustID INT > > SET VAR vCount INT = 0 > > DROP CURS wdeliver > DECLARE wdeliver CURSOR FOR SELECT servid, saledate FROM service + > WHERE custid = .vcustid AND BalDueS <= 0.50 > OPEN wdeliver > WHILE 1 = 1 THEN > FETCH wdeliver INTO vServid, vDate > IF SQLCODE = 100 THEN > BREAK > ENDIF > > < a bunch of tests in here to count invoices. If it meets a > criteria then: > SET VAR vcount = (.vcount+1) > > > > ENDWHILE > DROP CURS wdeliver > > CLEAR VAR vDate, vServid > RETURN .vCount > > RETURN > > > > > Karen > > > > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
