CODEWITHSUNDEEP
sql-servert-sqldynamic-sql
phroureo
phroureo

Reputation: 379

Use Dynamic Syntax in a View or a Function

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

Upvotes: 0

Views: 128

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

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

Upvotes: 1

Related Questions