Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here
DynDeveloper.com

DynDeveloper.com

Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now!Log In
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?

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

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
site