Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics SL forum
Answered

Syntax to return a value from a stored procedure to Dynamics

Posted on by 30

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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Syntax to return a value from a stored procedure to Dynamics

    This should do it.

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

    serr8 = SqlFetch(c1, strSQL, bVariableName)

  • Suggested answer
    Apps Mexico Profile Picture
    Apps Mexico 1,090 on at
    RE: Syntax to return a value from a stored procedure to Dynamics

    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

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Syntax to return a value from a stored procedure to Dynamics

    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

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,989 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,588 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans