How to create a SQL query using CloudPage input?

Question

I have a simple CloudPage using the Smart capture form. The form collects a number and writes that number into a column called 'input_number' in a data extension.

Is it possible to fetch the 'input_number' and use that number in a SQL query, like a dynamic field?

Example:

The 'input_number' is 200.

create this SQL query:

SELECT TOP 200
customer_ID

FROM
CUSTOMER_INFO 

The 'input_number' is 1000.

create this SQL query:

SELECT TOP 1000
customer_ID

FROM
CUSTOMER_INFO 

The SQL query created will write data to a data extension, but only contain the rows specified by the 'input_number'

I have tried to look at: https://developer.salesforce.com/docs/atlas.en-us.mc-programmatic-content.meta/mc-programmatic-content/ssjs_queryDefinitionFunctions.htm

but I don't know SSJS, so I am not even sure that 'Query Definition Functions' can help me regarding this.

Answer 1

Option 1 is the API and SSJS.

You can inject SSJS variables e.g. into a CreateRequest / UpdateRequest in the SOAP API. Naveen's link should help with this option.

Option 2 is to populate a data extension field with the input ("1000") and then use a subselect:

SELECT TOP (
    SELECT 
    CAST("Input_number" AS INT) AS 'intValue' 
    FROM "settings data Extension")
"customer_ID"
FROM "CUSTOMER_INFO"