How do I declare a variable in @body for SQL Server?

Question

I am trying to send an email through SQL Server. Everything is working however I can not get the @body to accept the variables I am declaring.

I also tried calling a new variable and setting that to the @body but I am running into the same issue.

Declare @Body varchar(max),    
      @TableHead varchar(max),
      @TableTail varchar(max),
      @message as varchar(max)     

Set @message=
    (

    DECLARE @CNT as int, @SLS as NVARCHAR(10)
    select  [employeeid], [Sales]
    into #loctempemployee from tblEmployees  

    Set @CNT =  (Select COUNT (Distinct EmployeeID) from #loctempemployee)

    SELECT tr.Principal As [TD], tr.[Company Name] As [TD], ai.[Action Item] As [TD], ai.Owners As [TD], ai.[Due Date] As [TD], ai.Updated As [TD]
    FROM [tblActionItem] ai
    INNER JOIN tblTripReport tr ON ai.TripReportID = tr.tripreportID
    INNER JOIN tblCustomers cu ON cu.CustomerID = tr.[Customer ID]
    INNER JOIN tblEmployees em ON em.EmployeeID = cu.EmployeeID
    WHERE em.Sales = (Select sales from #loctempemployee Where EmployeeID = (Select top 1 EmployeeID from #loctempemployee))
    For XML raw('tr'), Elements

    Delete #loctempemployee Where EmployeeID = (Select top 1 EmployeeID from #loctempemployee) 
    set @CNT = @CNT -1;

    End

    drop table #loctempemployee 
)

Select @Body = (@message)

Answer 1

If I understand you correctly, you're trying to assign the result of the SELECT to @message. The syntax you are using is not correct. You can't run a batch of statements within a SET like that in TSQL. You can assign @message the results of a query so long as your query produces a string result. So I think you probably want to change your code to be something like this (pulling the other statements out of your SET statement).

Declare @Body varchar(max),    
      @TableHead varchar(max),
      @TableTail varchar(max),
      @message as varchar(max);     


    DECLARE @CNT as int, @SLS as NVARCHAR(10);
    select  [employeeid], [Sales]
    into #loctempemployee from tblEmployees;  

    Set @CNT =  (Select COUNT (Distinct EmployeeID) from #loctempemployee);

Set @message=
    (
    SELECT tr.Principal As [TD], tr.[Company Name] As [TD], ai.[Action Item] As [TD], ai.Owners As [TD], ai.[Due Date] As [TD], ai.Updated As [TD]
    FROM [tblActionItem] ai
    INNER JOIN tblTripReport tr ON ai.TripReportID = tr.tripreportID
    INNER JOIN tblCustomers cu ON cu.CustomerID = tr.[Customer ID]
    INNER JOIN tblEmployees em ON em.EmployeeID = cu.EmployeeID
    WHERE em.Sales = (Select sales from #loctempemployee Where EmployeeID = (Select top 1 EmployeeID from #loctempemployee))
    For XML raw('tr'), Elements
    );


Delete #loctempemployee Where EmployeeID = (Select top 1 EmployeeID from #loctempemployee); 
    set @CNT = @CNT -1;

drop table #loctempemployee; 

Select @Body = (@message);

Also, are you doing this in a loop that you're not showing? There is an END that does not have a corresponding BEGIN, and that would also explain the line set @CNT = @CNT -1;.