LonnieNelson 8/17/2018 12:01:22 PM

Dexterity Prototype Stored Procedure

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?

/****** Create Stored Procedure:  uspGetCustomerPOAndInvoice     ******/
CREATE PROC [dbo].[uspGetCustomerPOAndInvoice]
@CustomerID varchar(20),
@InvoiceDate datetime,
@SOPType integer

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



Version: GP 2015
Section: Dexterity, SQL Scripts

Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables