Create dynamic sql statement based on the output of a select in sql server

Question

I have a function that takes any date and translates it to another date within a different year (the point of it is to preserve the weekday, week number and month, but thats not really relevant). I use it to translate a list of dates and create a temporary table that maps the original date to the mapped date using the function. The query looks like this:

Select  InputDates.Date as InputDate, dbo.GetFutureDate(InputDates.Date,2012) as PastDate
        INTO #DateMap
        FROM InputDates

InputDates is the list of dates that I need to translate. dbo.GetFutureDate is the translation function.

As you can see the year is hardcoded, which is what I am trying to change. I have a list of years in another table. I want to create a dynamic sql statement with a series of SELECT statements like the one above, changing the year based on the list of years I have and then combine them together using Union All.

What's the best way for me to do this?

VBlades · Accepted Answer · 2014-05-21 21:54:22Z

1

Hopefully I am understanding you correctly, but why don't you create a cross join with the year list you have and pass the function the year column from the year list.

Select  InputDates.Date as InputDate, dbo.GetFutureDate(InputDates.Date,YearInYearList) as PastDate
    INTO #DateMap
    FROM InputDates, YearList

YearList is your table name with the years in them, YearInYearList is the column name from the table. This should produce exactly what you want without the UNION overhead.

answered May 21, 2014 at 21:54

VBlades

2,2511 gold badge14 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3399111 · Accepted Answer · 2014-05-21 19:30:14Z

0

Select InputDates.Date as InputDate, dbo.GetFutureDate(Date.Date,(select year from YourYearTable)) as PastDate INTO #DateMap FROM Dates

answered May 21, 2014 at 19:30

user3399111

1431 gold badge2 silver badges12 bronze badges

1 Comment

Matt Kagan Over a year ago

This does works only if I select one year, like if I do this Select InputDates.Date as InputDate, dbo.GetFutureDate(Date.Date,(select Top 1 year from YourYearTable)) as PastDate INTO #DateMap FROM Dates If there are multiple years I get the following message: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Umachandar - Microsoft · Accepted Answer · 2014-05-21 22:59:03Z

0

You can use the APPLY operator in SQL Server to achieve what you want. Basically, the APPLY operator allows you to reference a column from the outer query and it converts to a join or outer join.

Ex:

select y.year, t.* into #DateMap from years as y cross apply ( Select InputDates.Date as InputDate, dbo.GetFutureDate(InputDates.Date, y.year) as PastDate FROM InputDates ) as t

See Books Online for more details on the APPLY operator.

answered May 21, 2014 at 22:59

Umachandar - Microsoft

4843 silver badges5 bronze badges

Collectives™ on Stack Overflow

Create dynamic sql statement based on the output of a select in sql server

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related