Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

Calling a stored procedure.

edwardr28531298
Level 2
Level 2

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!

1 Accepted Solution
kapilKochar
Correct answer by
Community Advisor
Community Advisor

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

View solution in original post

1 Reply
kapilKochar
Correct answer by
Community Advisor
Community Advisor

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

View solution in original post