I am slowly bur surely getting a grip on calling SQL stored procedures using a dexterity prototype stored procedure. I have been able to return a value such as a record count or a name and address. What I need to do next is to be able to return a data set from a select statement to the calling Dexterity sproc. So, if a select statement returns 36 rows, I need to be able to look at all of the information from all three rows. Is there a way to do this?
One of the reasons I am trying to do this is we have an external process using visual studio that uses the stored procedures. We have some processes that run inside of GP that have been developed in dexterity and I would like to be able to use the same code to run both processes. That way, I have a single place to change any business logic regardless of whether the process is being initiated by the external process or by the GP user in Dexterity.
Here is a sample of a SQL stored procedure and the SQL script to call it that returns 36 rows. How can I run this via a Dexterity prototype stored procedure and be able to see all 36 rows in the resulting data set?
USE [SSL]
GO
/****** Create Stored Procedure: uspGetCustomerPOAndInvoice ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[uspGetCustomerPOAndInvoice]
(
@CustomerID varchar(20),
@InvoiceDate datetime,
@SOPType integer
)
AS
select sopnumbe as [SOP Number]
,custnmbr as [Customer ID]
,cstponbr as [Customer PO Number]
from sop30200
where custnmbr = @CustomerID
and docdate = @InvoiceDate
and soptype = @SOPType
/*
declare @CustomerID varchar(20)
declare @InvoiceDate datetime
declare @SOPType integer
set @CustomerID = 'XXXX'
set @InvoiceDate = '2/1/2017'
set @SOPType = 3
exec uspGetCustomerPOAndInvoice @CustomerID, @InvoiceDate, @SOPType
*/
Thanks,
Lonnie