Calling SQL Server stored procedures from Microsoft Dexterity - revisited

Back in January of 2009, I wrote an article showing a method of calling a SQL Server stored procedure from Dexterity by calling a stored procedure prototype script (sproc) in sanScript.

See, Calling SQL Server stored procedures from Microsoft Dexterity (January 26, 2009).

This sproc tells the runtime engine (Dynamics.exe) that it must connect to either the system or company database to run the stored procedure of the same name. I particularly like this method because it basically it shields the developer from dealing with connections, etc. Also, the procedure executes under the user's security context.

However, there are times when it is necessary to go the extra mile, especially if you have to call procedures you do not want to prototype into your code as a sproc - case in point, calling third party stored procedures, or eConnect stored procedures.

Enter the use of Dexterity SQL library functions and pass-through SQL. The Dexterity SQL library functions are well documented in the help file. If you are familiar with ADO.NET or standard ADO, the steps are pretty similar:

1. Create a connection and set the database context for the stored procedure (akin to a connection string)
2. Create your prepared SQL statement to run the connection, including parameters.
3. Execute and fetch the recordset.


Here's a simple example (for simplicity sake, basic error handling implemented):

1) Write your stored procedure and grant access to the SQL Server DYNGRP role. This will make your stored procedure accessible from your Dexterity application, avoiding SQL Server permission issues. Our stored procedure will need to accept a service call number as a parameter, then return the total amount to be billed on the service call.

dbo.uspGetServiceCallTotal

IF OBJECT_ID ( 'dbo.uspGetServiceCallTotal', 'P' ) IS NOT NULL
  DROP PROCEDURE dbo.uspGetServiceCallTotal
GO

CREATE PROCEDURE dbo.uspGetServiceCallTotal
  @IN_callnbr char(20) = NULL,
  @IN_service_type smallint,
  @INOUT_serviceTotal numeric(19,5) output
AS

SELECT @INOUT_serviceTotal = TOTAL FROM dbo.SVC00200
WHERE CALLNBR = @IN_callnbr and SRVTYPE = @IN_service_type
GO

GRANT EXECUTE ON dbo.uspGetServiceCallTotal TO DYNGRP
GO

2) The following is a sample Dexterity calling script that retrieves the parameter result as a dataset and issue a simply warning with the returned value:

in string CallNumber;
in integer CallType;

local long sqlConn;
local long sqlStatus;
local text sqlStmt;
local string paramlist = "%1, %2, %3 output;";
local string outparam1  = "@outparam";
local string outparam1Type = "numeric(19,5)";
local currency lServiceTotal;
 
sqlStatus = SQL_Connect(sqlConn);
if sqlStatus = OKAY then
     clear sqlStmt;
 
     {setup the output parameter declaration line}
     sqlStmt = sqlStmt + "declare " + outparam1 + CH_SPACE + outparam1Type + CH_SEMICOLON + CRLF;
 
     {setup parameter list to pass into proc}
     substitute paramlist, SQL_FormatStrings(CallNumber), str(CallType), outparam1 + CRLF;  
     sqlStmt = sqlStmt + "exec dbo.uspGetServiceCallTotal" + CH_SPACE + paramlist + CRLF;
     sqlStmt = sqlStmt + "select " + outparam1;
     
     sqlStatus = SQL_Execute(sqlConn, sqlStmt);
     if sqlStatus = OKAY then
           sqlStatus = SQL_FetchNext(sqlConn);
           
           if sqlStatus <> 31 then
                sqlStatus = SQL_GetData(sqlConn, 1, lServiceTotal);
                warning str(lServiceTotal);
           end if;
     end if;
end if;

One of the things the runtime engine will do is run this procedure within the context of the current Microsoft Dynamics GP user connection to SQL Server, this is, another connection will not be created unless it's absolutely necessary. The technique I show above also seeks to declare the parameter list as a string with substitution patterns to reduce chances of SQL code injection. If your SQL procedure will return a larger data set, then you will need a while...do cycle to advance to the next record in the recordset.

Hope you find this useful.

Until next post!

MG.-
Mariano Gomez, MVP