web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / Syntax to return a val...
Microsoft Dynamics SL (Archived)

Syntax to return a value from a stored procedure to Dynamics

(0) ShareShare
ReportReport
Posted on by LeeRoy 38

Hi

Can someone please assist me with how the syntax in Dynamics should look like to return a value from a stored proc in sql so that I can use that value in a dynamics screen.

Please advise me on this 

Regards,

Lee'Roy

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Community Member Profile Picture
    Community Member on at

    Hi Lee Roy,

    The value from stored procedure can be return to Dynamics Screen through output parameter and this can be accomplished through SQLFetch1 statement

    Please follow the steps:

    1. Create Stored procedure with using output Parameter
    2. Create another stored procedure to call the first procedure with one parameter.

    Given below the syntax :

    --To create a first Procedure

    IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME=’sp_one’ AND TYPE =’P’)

    DROP PROCEDURE ‘sp_one’

    GO

    CREATE PROCEDURE sp_one

    @Proj varchar(5) NOT NULL,

    @value varchar(32) NOT NULL OUTPUT

    AS

    SELECT TOP 1 @value = Pjt_entity from PJPENT WHERE PROJECT = @proj

    RETURN

     

    --To create a second procedure for getting output value and use this to dynamics

    IF EXISTS(SELECT * FROM SYS.OBJECTS WHERE NAME=’sp_two’ AND TYPE =’P’)

    DROP PROCEDURE ‘sp_two’

    GO

    CREATE PROCEDURE sp_two

    @Task varchar(32)  NOT NULL

    AS

    EXECUTE sp_one ‘xxxxx’,@Task output

    SELECT @Task

     

    Code in SL Customization,

    For Eg: 

    Dim Value as string

    Strquery = "EXEC sp_two ‘xxx’”

    serr = SqlFetch1(Csr_xxxx, Strquery, Value)

    Call MessBox("Task =” &sparm(Trim(Value)), MB_ICONINFORMATION,bpes.cpnyid)

     Call SqlFree(Csr_xxxx).

    Please let me know how it goes.

     

    Thanks,

    Srikanth

  • Suggested answer
    Apps Mexico Profile Picture
    Apps Mexico 1,090 on at

    Hi,

    You have to add a DH module with the structure of the fields that the store procedure returns and execute the sqlfetch1 function.

    serr_xFEARDoc = SqlFetch1(CSR_xFEARDoc, "storeprocedurename" & SParm(bxFEARDoc.BatNbr), bxFEARDoc, LenB(bxFEARDoc))

    Option Explicit
    Type xFEARDoc
    BatNbr As String * 10
    BatSeq As Long
    CpnyID As String * 10
    Crtd_DateTime As Sdate
    Crtd_User As String * 47
    CustID As String * 15
    DocType As String * 2
    LUpd_DateTime As Sdate
    LUpd_User As String * 47
    MetodoDePago As String * 200
    NumeroDeCuenta As String * 200
    RefNbr As String * 10
    Serie As String * 20
    ShipToId As String * 10
    Terms As String * 100
    User1 As String * 60
    User2 As String * 60
    User3 As Double
    User4 As Double
    User5 As String * 10
    User6 As String * 10
    User7 As Sdate
    User8 As Sdate
    User9 As Long
    End Type
    Public bxFEARDoc As xFEARDoc, nxFEARDoc As xFEARDoc, txFEARDoc As xFEARDoc
    Public CSR_xFEARDoc As Integer

    Public serr_xFEARDoc As Integer

  • Community Member Profile Picture
    Community Member on at

    This should do it.

    strSQL = "exec [spName] <param>,<param>"

    serr8 = SqlFetch(c1, strSQL, bVariableName)

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics SL (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans