procedures in RTDM

[RTDM_User]

Hi Team,

I wanna excute a sql procedure using the RTDM,

Problem - so in order to do it i need to send a varibles from the flow to that procedure that was built in the DB (sql server) and get a varibles back to the flow,

is this possible ? if yes, what is the best solution for it?

Thanks in advance,

RTDM_User

[needle]

Hi RTDM_User,

There are two options you can use to interact with a database in RTDM. The first option is to use the standard data process node, which can be used to either READ, INSERT or UPDATE data in a table (or view). Since you mention procedures, this is not going to work for you (it can't run execute, it will only do SELECT, INSERT & UPDATE). The second option to interact with a database is to write a custom process in DS2, where you can use FedSQL to connect to a data source. With FedSQL you can for example use SQL pass-through (example: https://blogs.sas.com/content/sastraining/2016/07/20/jedi-sas-tricks-explicit-sql-pass-through-in-ds...) to leverage database functions (like a stored procedure).

I'm not a DS2 expert, but I hope this leads you the right way and maybe someone else in the community has an actual solution in DS2 code for running stored processes already.

Here are a few other useful DS2 references:

Mastering the SAS DS2 Procedure: Advanced Data Wrangling Techniques, a book by Mark Jordan: http://support.sas.com/jordan

SAS 9.4 FedSQL Language Reference: http://support.sas.com/documentation/cdl/en/fedsqlref/67364/HTML/default/viewer.htm

SAS 9.4 DS2 Language Reference: http://support.sas.com/documentation/cdl/en/ds2ref/68052/HTML/default/viewer.htm

[Dmitry_Alergant]

There is a way to make it work with standard data processes, but with some limitations.

You'll need to create a new database table ("input table") where you'd insert your input parameters for a procedure. Plus some unique request identifier (String) to differentiate between multiple concurrent requests.

Then you'll need to create a SQL Server view that calls the procedure for every record in the input table, and returns its result, alongside the original request identifier from the source table.  If I'm not mistaken, you may need to use the "OPENQUERY" function in the view (can't say exactly, i'm much more of Oracle guy than sql server).

In RTDM, you can then have a sequence of 3 Data Processes:

• Insert your input values to the input table, and use Event.Identity as a request identifier
• Read from the view, and filter on the request identifier = Event.Identity as criteria variables.
• Delete a record from the input table (again using Event.Identity as a request identifier).

The catch is that you need to make sure the procedure you are calling doesn't have any unsafe side-effects and is safe if sometimes called extra times repeatedly.  With this solution,  there will be situations where two or more requests are processed concurrently, and there will be more than one record in the input table - so the procedure will be called extra times. Depending on your use case and the nature of this procedure, it may or may not be OK.

If it's not OK, then the way to go develop is a DS2 or Groovy script process that will call the procedure with explicit SQL.