Using parameters in Stored procedures

[Ceciliavh]

Good morning

We currently make extensive use of process nodes in our marketing campaigns, but I read somewhere that you should rather make use of Stored procedures as best practice. The process nodes obviously have quite a bit of customization with regards to filters and I am unsure how replace that with stored procedure functionality whilst maintaining flexibility of the process nodes.

Example:

(Process node)

Select business_key

from sales_table

where product = 'Cosmetics'  -- hardcoded

I would like to replace the 'Cosmetics' value with a parameter, but where would that value come from? I wouldn't want to use a Custom detail group on the Campaign definition as that would be restricting the marketer to only one type of selection in a campaign.

Thanks in advance..

[JamesAnderson]

Hi,
When you define your stored process you can create prompts. These prompts can be different types (free text, check boxes, single/multiple choice drop down lists etc). These prompts get automatically created as macro variables in the STP code, and the values entered by the user passed in as the value. These prompts are displayed inside the node in CI studio for the user to enter values.
So lets say you have a known list of defined values for products (Cosmetics, Hardware, Electrical etc) you could create an STP with a prompt called Product_Type, set this prompt to be a static list and enter those 3 values for the user to choose from). Then in your SAS code you change it to:

Select business_key

from sales_table
where product = &Product_Type;
There is a lot of flexibility in the types of prompts you can use, including using lookup table to get the defined values - check out the MA user guide (version 6.5 Pg 142) for further info, as well as the SAS Platform documentation for creating STPs.
Regards
James

[Ceciliavh]

Thank you James, this worked a charm!

Do you perhaps know if you could create conditional parameters, i.e. what is put into the first parameter is used to filter the second?

e.g.

Param1: select Country

Param2: Only shows Products sold in that Country

[JamesAnderson · Accepted solution]

Yes - you can use a feature called Cascading prompts for this.
Regards
James