Calling a stored procedure.

[edwardr28531298]

Hello all. I have two questions, SQL related.

what are some ways to use the SQL activity? is it possible to write my own SQL in that activity to query a list of recipients instead of using the query activity?

second, is there a way to call a stored procedure in adobe campaign classic?

any info on this will be much appreciated!

Thank you!

[kapilKochar · Accepted solution]

Hi Edward,

Is it possible to write my own SQL in that activity to query a list of recipients instead of using the query activity? - Yes, You can use SQL activity  for this. As an example:

E.g

UPDATE NmsDelivery SET iRoutingDeliveryId = <%= vars.deliveryId %> WHERE iDeliveryId=<%= vars.iDeliveryId %>

Also, if you can use normal javascript activity and use functions like below to Executes an SQL script.

sqlExec (query [, value1, ... ])

sqlExecOnDataSource (query, dataSource [, value1, ... ])

sqlGetString (query [, value1, ... ])

E.g :

var firstName = sqlGetString('select sfirstName from nmsRecipient where sEmail =$(sz) order by tsCreated DESC LIMIT 1', email)

var sql4 = "INSERT INTO Reporting (sClientdesc,sClient) values ('" + client.@description.toString() + "','" + client.@clientCode.toString() +"')";

sqlExec(sql4);

REFER: https://docs.campaign.adobe.com/doc/AC/en/jsapi/p-1.html  for more SQL functions.

I hope this helps.

Thanks,

Kapil Kochatr