SQL Server 2017 - Database mail stored procedure

Question

I have a stored procedure which basically I want to do the following:

Create temp table (if not exists) and populate with data
Output the query to SSMS, and assigning the query a variable (@sql)
Using the query, e-mail the contents of the query to the recipients

My script is this:

Create Procedure ListDaysofYear(@year as integer)
as 
    Declare @sql as varchar(200), @DBqry as varchar(200),
            @tab as char(1) = char(9)
    Declare @dayofyear as bigint = 1
    Declare @monthofyear as int = 1
    Declare @day as int = 1
    Declare @curDate as datetime
    Declare @DB as varchar(40)
    Declare @sql2 as varchar(40)

    Set @curDate = datefromparts(@year, @monthofyear, @day)
    Set @DB = 'msdb'

    IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL
        DROP TABLE ##daysofYear
        --Print 'YES'
    ELSE
        CREATE TABLE ##daysofYear
        (
             cDate DATETIME PRIMARY KEY NOT NULL,
             cMonth VARCHAR(20) NOT NULL,
             cDay VARCHAR(20) NOT NULL
        )

    WHILE year(@curDate) = @year
    BEGIN
        -- Insert rows based on each day of the year
        INSERT INTO ##daysofYear (cDate, cMonth, cDay)
        VALUES( (@curDate),
                (DATENAME([MONTH], @curDate)),
                (DATENAME([WEEKDAY], @curDate)) )

        SET @curDate = @curDate + 1
    END 

    --Output file to SSMS query window
    Select dy.* from ##daysofYear dy;

    Set @sql = 'Select dy.* from ##daysofYear dy;'
    Set @sql2 = 'Use ' + @DB + '; Exec msdb.dbo.sp_send_dbmail
             @profile_name = ''Notifications'',
             @recipients = ''mikemirabelli6@hotmail.com'',
             @attach_query_result_as_file = 1,
             @query_attachment_filename = ''daysofyear.txt'',
             @query_result_separator = '',
             @body = ''The attached output file - DaysofYear table'',
             @query = ''Select dy.* from ##daysofYear dy'' ;'

    --Execute sp_sqlexec @sql
    Exec(@sql2)

Basically when I run the execute line:

Exec dbo.ListDaysofYear 2018 ;

I get the following message the first time:

Msg 208, Level 16, State 0, Procedure dbo.ListDaysofYear, Line 25
[Batch Start Line 52] Invalid object name '##daysofYear

I believe it’s related to the "DROP TABLE" part of the T-SQL.

Thanks

I noticed that when I try to write anything as simple in the @query parameter for send mail SP, I get this message, otherwise that second error NEVER fires. SOmething whenever I write a simple select (Ex Select 1), always returns this message. — Mike Mirabelli, Nov 20 '18 at 23:33
I don’t think temp tables are accessible via dynamic SQL. Can you not use real table and clean them up at the end? — sheeni, Nov 20 '18 at 23:47
I thought of that but when I tried using a regular table , the same result - the IF is not firing as intended within the query. It's strange when I do the following : 'IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL Print 'Exists' ELSE Print 'Does Not Exist' IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL Drop Table ##daysofYear ELSE Create Table ##daysofYear (id# int primary key); ' And I go back and forth it works as expected. — Mike Mirabelli, Nov 21 '18 at 00:06
Is that the normal syntax for global tables ? Do you mind explaining the difference ? I can try it when I log back in and let you know thanks @sheeni — Mike Mirabelli, Nov 21 '18 at 00:30
NVM, think i found the issue, IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL <-- here you are dropping the table if exisit but doesn't create it so it throws an error in line 25 where it tries to insert data (to a table you dropped). i suggest replacing drop table with TRUNCATE TABLE. Let me know if this works i will add it as the answer — sheeni, Nov 21 '18 at 00:46
Does truncate table basically clear the contents of the table but keep the structure ? — Mike Mirabelli, Nov 21 '18 at 01:03
Great. Makes sense I’ll implement that. I guess an alternative would be to move the insertion after the creation although I think if statements unlike in other languages only handle 1 command right ? — Mike Mirabelli, Nov 21 '18 at 01:07
you can have multiple statements if you add BEGIN and END underneath your if and else. You can have multiple lines of code between BEGIN and END — sheeni, Nov 21 '18 at 01:11

score 1 · Accepted Answer · answered Nov 21 '18 at 01:12

Think i found the issue:

IF OBJECT_ID('tempdb.dbo.##daysofYear','U') IS NOT NULL <-- here you are dropping the table if exisit but doesn't create it so it throws an error in line 25 where it tries to insert data (to a table you dropped). i suggest replacing drop table with TRUNCATE TABLE.

SQL Server 2017 - Database mail stored procedure

1 Answers1