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

Question

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?

@user3115933 no, it's not. We're talking about the second statement, not the first. — Thom A, Feb 20 '18 at 15:23
The stored procedure must itself declare `@Body2` explicitly as an output parameter. You can't just pluck out arbitrary variables inside the body from the call site. — Jeroen Mostert, Feb 20 '18 at 15:25
@JeroenMostert Thanks but I lost you here. Can you help on how to re-write that part? — user3115933, Feb 20 '18 at 15:27
Possible duplicate of [Execute stored procedure with an Output parameter?](https://stackoverflow.com/questions/1589466/execute-stored-procedure-with-an-output-parameter) — Jacob H, Feb 20 '18 at 15:28
I recommend consulting the [fine manual](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql#f-using-output-parameters). — Jeroen Mostert, Feb 20 '18 at 15:28

score 2 · Accepted Answer · answered Feb 20 '18 at 15:29

2

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' ;

answered Feb 20 '18 at 15:29

Tab Alleman

31,483
7
36
52

I tried your solution and I am now getting the following error from the SQL job: "Procedure ITB_SendEmail has no parameters and arguments were supplied."Any ideas where to look for the cause/s of the error? – user3115933 Feb 20 '18 at 16:10
Sounds like you did not set up the output parameter correctly in the first stored procedure. Edit your question and post the new code. – Tab Alleman Feb 20 '18 at 16:23
I did not edit the Stored Procedure. I went through the link you provided regarding output parameters but I am still a little confused on how to implement it in my scenario. – user3115933 Feb 20 '18 at 16:37
What part of it is confusing you? The link provides sample code for how to edit your stored procedure. You need to make `@Body2` an OUTPUT parameter in your stored procedure, and not a local variable the way you have it now. – Tab Alleman Feb 20 '18 at 16:40
Thanks. I will look into that again and report back. – user3115933 Feb 20 '18 at 16:42
Thanks for all your support. Worked out perfectly! – user3115933 Feb 20 '18 at 16:55

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

1 Answers1