SQL Tip - Return a dynamic range of years to populate a drop-down list

There are times when you need to populate a drop-down list with a list of years, for example when asking a user to select the relevant financial year where a full date picker is not appropriate. You have a number of options, such as hard coding the list of years, or using a data source such as CSV or XML file. When you need to build a list of years that can change automatically, one of the easiest ways to do this is by creating a stored procedure in a SQL Server database. This article provides an example stored procedure you can use to implement this.

The below Stored Procedure returns a range of years, with the ability to configure how many years before the current year to include, and how many years into the future to include.

You will need access as a DBA to the target SQL Server database to create the stored procedure, and administrator access to the Infiniti environment to set up a new data source.

After creating the above stored procedure in your SQL Server database, you need to add it in Infiniti Manage. Configure a Data Source with a connection string pointing to the SQL Server database, and then add a new data object that references the stored procedure. Once configured you can add a data source question to your project in Design and configure the @PreviousYears and @FutureYears key fields to pass in how many years into the past and future are required to be returned.