WHILE Loop in Input Data's SQL Editor

[jheit]

I'm currently working on automating a process that doesn't allow me to use iterative macros or In-Database tools. The process is responsible for grabbing data for a user-defined number of calendar years (YearQuantity) based on a user-defined most recent calendar year (Year1). I envisioned this process using a Counter variable (in a SQL statement) to iterate through all of the proper Calendar years, which are included as criteria in WHERE statements.

Here's the SQL code that I thought would work in the Input Data's SQL Editor:

DECLARE @Counter INT
SET @Counter=0
WHILE (@Counter < %Question.YearQuantity%)
BEGIN
SELECT Database.Table.*
FROM Database.Table

WHERE Database.Table.YYYYMMDD LIKE '%Question.Year1%-@Counter %'

Unfortunately, this code doesn't work despite trying various methods with Pre & Post SQL statements. I would greatly appreciate if somebody could share a solution that doesn't use iterative macros or In-DB tools. Thanks!!

[BrandonB]

Can you use a Generate Rows tool to create a row up to the user defined calendar years. The generate rows tool creates an ID field that can be used in building the actual query. The column of queries could then be passed into a regular batch macro rather than an iterative macro. 

[jheit]

I should add that I'm unable to use any macros within this workflow. I'm trying to perform all limiting operations within the SQL Editor to lower runtimes, as not all data needs to be read in.

[jheit]

I have updated my original post due to formatting problems. It now includes the WHERE statements I'm using to limit the dates by the leading 4 characters.

[BrandonB]

If you can't use any macros, I would try to build the query using regular alteryx tools and then pass the result into a Dynamic Input tool. I have a feeling that your issue is using the variables within the query. If you have the actual query built within the workflow and passed in its executable state in a Dynamic Input tool it should work. 

[jheit]

Could you elaborate more on this? I am unsure what you meaning by building my query using Alteryx tools and then passing its executable state into a Dynamic Input.