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

Applies to Infiniti v8.1 or later
 

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.
CREATE PROCEDURE [dbo].[GetRangeOfYearsFromToday]
    @PreviousYears int,
    @FutureYears int = 0
AS
BEGIN
    SET NOCOUNT ON;

    WITH yearlist AS 
    (
        SELECT YEAR(GETDATE()) - @PreviousYears AS Year
        union all
        SELECT yl.Year + 1 AS Year
        FROM yearlist yl
        WHEREe yl.Year + 1 <= YEAR(GetDate()) + @FutureYears
    )
    
    SELECT Year FROM yearlist ORDER BY year DESC;
END
GO
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.

Example of data source filters to be used within your project:
Filter example image

Example of data source drop down within Produce:
Example of drop down

 

Related Articles

 

 

Keywords

 

sql tipp return dynamic range years populate drop-down list dataobject

 
stored proc list year calendar date