Search code examples
sqlsql-serveruser-defined-functionssp-send-dbmail

How to correct the scalar variable declaration error in my SQL job?

I am using SQL Server 2012 and I have the following stored procedure which is returning the required output in the form of a HTML table:

CREATE PROCEDURE dbo.ITB_SendEmail
AS
BEGIN
DECLARE @Body NVARCHAR(MAX),
        @Body2 NVARCHAR(MAX),
        @TableHead VARCHAR(1000),
        @TableTail VARCHAR(1000)

SET @TableTail = '</table></body></html>' ;
SET @TableHead = '<html><head>' + '<style>'
    + 'td {border: solid black;border-width: 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font: 11px arial} '
    + '</style>' + '</head>' + '<body>' + 'Report generated on : '
    + CONVERT(VARCHAR(50), GETDATE(), 106) 
    + ' <br> <table cellpadding=0 cellspacing=0 border=0>' 
    + '<tr> <td bgcolor=#E6E6FA><b>StayYear</b></td>'
    + '<td bgcolor=#E6E6FA><b>Market</b></td>'
    + '<td bgcolor=#E6E6FA><b>Jan</b></td>'
    + '<td bgcolor=#E6E6FA><b>Feb</b></td>'
    + '<td bgcolor=#E6E6FA><b>Mar</b></td>'
    + '<td bgcolor=#E6E6FA><b>Apr</b></td>'
    + '<td bgcolor=#E6E6FA><b>May</b></td>'
    + '<td bgcolor=#E6E6FA><b>Jun</b></td>'
    + '<td bgcolor=#E6E6FA><b>Jul</b></td>'
    + '<td bgcolor=#E6E6FA><b>Aug</b></td>'
    + '<td bgcolor=#E6E6FA><b>Sep</b></td>'
    + '<td bgcolor=#E6E6FA><b>Oct</b></td>'
    + '<td bgcolor=#E6E6FA><b>Nov</b></td>'
    + '<td bgcolor=#E6E6FA><b>Dec</b></td>';

SET @Body = ( SELECT td = StayYear, '',
                        td = Market, '',
                        td = Jan, '',
                        td = Feb, '',
                        td = Mar, '',
                        td = Apr, '',
                        td = May, '',
                        td = Jun, '',
                        td = Jul, '',
                        td = Aug, '',
                        td = Sep, '',
                        td = Oct, '',
                        td = Nov, '',
                        td = Dec, ''
              FROM  View1               
                FOR   XML RAW('tr'),
                      ELEMENTS
            )


SET  @Body2 = @TableHead + ISNULL(@Body, '') + @TableTail

SELECT  @Body2

END

I have now set up a SQL job which is supposed to execute this stored procedure and emails the output to a recipient. My SQL Server job runs a T-SQL query (see below):

DECLARE @GeneratedHTML NVARCHAR(MAX); 
EXEC ITB_SendEmail @GeneratedHTML = @Body2 OUTPUT

EXEC sp_send_dbmail 
  @profile_name='BI',
  @copy_recipients ='',
  @recipients='xxx@yyyy.com',
  @subject='ITB',
  @body= @GeneratedHTML ,
  @body_format = 'HTML' ;

However, the SQL Server job is failing with the following an error message:

Must declare the scalar variable @Body2. [SQLSTATE 42000] (Error 137). The step failed

I cannot figure out the scalar variable declaration issue. What am I doing wrong?

Solution

  • The error is not referring to the variable in your Stored Procedure code if that's what you're thinking.

    It's saying you need to DECLARE @Body2 in your Execution Batch. However you have more problems than that. You are trying to access an output parameter of your stored procedure, but in your stored procedure you haven't got an output parameter.

    Reading the documentation on Output parameters would be a good start.

    Assuming you correctly set @Body2 as an output parameter in your stored procedure, then you simply need to reverse their order in the execution statement:

    DECLARE @GeneratedHTML NVARCHAR(MAX); 

EXEC ITB_SendEmail  @Body2 = @GeneratedHTML  OUTPUT

EXEC sp_send_dbmail 
  @profile_name='BI',
  @copy_recipients ='',
  @recipients='xxx@yyyy.com',
  @subject='ITB',
  @body= @GeneratedHTML ,
  @body_format = 'HTML' ;