Qy8J2qbt

1--1) Create temp table (if not exists) and populate with data
2--2) Output the query to SSMS, and assigning the query a variable (@sql)
3--3) Using the query, e-mail the contents of the query to the recipients
4	
5Create Procedure ListDaysofYear(@year as integer)
6as 
7Declare @sql as varchar(200), @DBqry as varchar(200), @tab as char(1) = char(9)
8Declare @dayofyear as bigint = 1
9Declare @monthofyear as int = 1
10Declare @day as int = 1
11Declare @curDate as datetime
12Declare @DB as varchar(40)
13Declare @sql2 as varchar(40)
14
15Set @curDate = datefromparts(@year, @monthofyear, @day)
16Set @DB = 'msdb'
17
18IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL
19    Drop Table ##daysofYear
20    --Print 'YES'
21ELSE
22    Create table ##daysofYear
23    (cDate datetime Primary key not null,
24    cMonth varchar(20) not null,
25    cDay varchar(20) not null)
26
27WHILE year(@curDate) = @year
28BEGIN
29Insert into ##daysofYear (cDate, cMonth, cDay)
30Values 
31--Insert rows based on each day of the year
32( (@curDate),
33(datename([MONTH],@curDate)),
34(datename([WEEKDAY],@curDate)))
35
36Set @curDate = @curDate + 1
37
38END 
39
40--Output file to SSMS query window
41Select dy.* from ##daysofYear dy;
42
43Set @sql = 'Select dy.* from ##daysofYear dy;'
44Set @sql2 = 'Use ' + @DB + '; Exec msdb.dbo.sp_send_dbmail
45     @profile_name = ''Notifications'',
46     @recipients = ''mikemirabelli6@hotmail.com'',
47     @attach_query_result_as_file = 1,
48     @query_attachment_filename = ''daysofyear.txt'',
49     @query_result_separator = '',
50     @body = ''The attached output file - DaysofYear table'',
51     @query = ''Select dy.* from ##daysofYear dy'' ;'
52
53--Execute sp_sqlexec @sql
54Exec(@sql2)
55	
56Exec dbo.ListDaysofYear 2018 ;