Use Dynamic Syntax in a View or a Function

Question

I have a dynamic query that I've written that looks like the following:

DECLARE @sql nvarchar(max)


SELECT @sql = 
'select distinct datetable.Date 
from (
select cast(DATEADD(day,-(a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a)),getdate()) as date) AS Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a

cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b

cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c

 cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d

 union all 

 select cast(DATEADD(day,(a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a)),getdate()) as date) AS Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a

cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b

cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c

 cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) datetable
where '
+
replace(replace(replace(stuff((SELECT ' or datetable.Date between cast(''' + cast(cast(hld1.StrDate as date) as nvarchar(12)) + N''' as date) and cast(''' + cast(cast(hld1.endDate as date) as nvarchar(12))+ N''' as date)
 '

from hld1  for xml path('')),1,3,''), '&lt;', '<'), '&gt;', '>'), '&#x0D;', char(13)) +
'order by datetable.Date '


--print @sql
EXEC sys.sp_executeSQL @SQL

HLD1 is a list of holidays, where each holiday has a start and end date. The query itself returns a list of dates that are defined as holidays. (The reason that I can't just select the start dates and union them to the end dates is that there could very feasibly be a holiday with three days, and the middle day wouldn't show up in either list.

However, I'm using this monstrosity to create a function, and, as part of the function, I want to be able to do something like "If the date is in this list, then do the following."

My original plan was to set up a view that would just be the list of dates; however, this is not possible, because it uses a variable, and variables aren't allowed in views.

My next thought was to create a function that would just return the list. However, when I put in the syntax to create it as a function, I get the error The last statement included within a function must be a return statement.

I am unsure what path I should pursue from here. The reason that I can't just make a table and list out the dates manually is that currently the list only extends through 2016. In addition, the holiday list (start and end dates) may be created/added differently for different databases that the end goal function would be added to and used on.

If you need more background/information, please let me know and I'd be happy to provide. I'm just learning as I go. :)

Edit 1: I found the following link, but it doesn't appear to apply in this case: Create A View With Dynamic Sql

Perhaps you should post the hld1 table. The table definition and a few rows of sample data should be sufficient. Along with that an explanation what you are trying to do would help. As of now I just can't understand what you need to accomplish. — Sean Lange, Jul 22 '16 at 18:27

Sean Lange · Accepted Answer · 2016-07-22T18:36:19.223

1

Why not just create a numbers or tally table as a persistent table or a view and avoid all this nastiness. 99% of this query is just generating a bunch of numbers.

For example, here is a view that performs 0 reads and will generate 10,000 rows of sequential numbers nearly instantly.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally
GO

There is your numbers portion. The next part would be to create the persistent dates table with the holidays and such like you are doing.

Here is an awesome article from my buddy Dwain Camps (RIP) about creating a calendar table. http://www.sqlservercentral.com/blogs/dwainsql/2014/03/30/calendar-tables-in-t-sql/

--EDIT--

Here is an example of having a table (#Something) with start and end dates for a holiday. This will list each date between those two dates. Unless I am missing something this should be pretty much what you are trying to do.

create table #Something
(
    HolidayName varchar(10)
    , StartDate date
    , EndDate date
)

insert #Something
select 'phroureo', '2016-03-01', '2016-03-05' union all
select 'Sean', '2016-07-04', '2016-07-05'

select HolidayName
    , StartDate
    , EndDate
    , DATEADD(day, t.N - 1, StartDate) as ResultDate
from #Something s
join cteTally t on t.N <= DATEDIFF(day, StartDate, EndDate) + 1
order by HolidayName

drop table #Something

edited Jul 22 '16 at 18:36

answered Jul 22 '16 at 18:18

Sean Lange

33,028
3
25
40

I'm not sure how this is helpful--I'm still going to have to create a function with some kind of dynamic SQL, and the number/date generation already takes less than a second to run. – phroureo Jul 22 '16 at 18:21
I could just be dumb, so if I'm not understanding you, please let me know. :) Also, the reason that a "Calendar" table isn't feasible is because of the way that SAP (the software we're using) functions with the HLD1 table. There is a UI for the users to update it. I believe I actually read that article early on in my search, and determined that it wasn't going to do what I needed. :( – phroureo Jul 22 '16 at 18:21
I don't really understand why you need dynamic sql. The code you posted is so verbose and full of repeating things it is hard to tell what your query is actually doing. – Sean Lange Jul 22 '16 at 18:22
The dynamic SQL pulls the holiday values stored in the database, and finds the days between the holiday start and end dates, inclusive. (For example, a spring break holiday could have a start date on 3/1 and an end date on 3/5 for a given year, and I would need to have the list of dates for 3/1,3/2/3/3, etc.) I can't use a calendar table, because the holidays are already stored in SAP, and the end user isn't capable of updating a manually created table from the database backend. – phroureo Jul 22 '16 at 18:25
On the contrary. A tally table would work perfectly for this kind of thing. You would find the date diff between the start and end, that is what you would use as your where clause. Then use DATEADD to add the value from the tally table to get all the dates. – Sean Lange Jul 22 '16 at 18:30
@phroureo I edited my response to include an example of what I think you might be trying to do. – Sean Lange Jul 22 '16 at 18:36
Awesome. Your example worked perfectly. Thanks for your patience. :) – phroureo Jul 22 '16 at 19:57
More importantly, do you understand how that works? The tally table is an unbelievably powerful tool. It has been termed the "Swiss Army Knife of t-sql". It is so simple yet can do so many thing way easier than otherwise would seem posssible. – Sean Lange Jul 22 '16 at 19:58
I think get it. It's just a different way of processing the query than I'm familiar with, but now that I see it written down, it makes sense. I'll need to do some experimentation of my own, but I think I can see lots of cases where this will be helpful! – phroureo Jul 22 '16 at 20:01
Here is a great article on the topic. http://www.sqlservercentral.com/articles/T-SQL/62867/ There are plenty of others but I like the way Jeff Moden writes. – Sean Lange Jul 22 '16 at 20:03

Use Dynamic Syntax in a View or a Function

1 Answers1