Stored procedure to generate HTML response doesnot work-CodePudding

I am trying to get the HTML out from the stored Procedure so I can use this stored procedure in my SSIS package to send email with this body.

This is the SQL query:

SELECT 
    cus.CustomerNumber AS CustomerNumber,
    cus.Location AS ReceivingLocation, 
    i.StrainName AS StrainName, i.StrainCode AS StrainCode,
    i.Age AS Age, i.Sex AS Sex, 
    i.Genotype AS Genotype, i.RoomNumber AS SentFrom,
    io.OrderQuantity AS OrderQuantity
FROM 
    [dbo].[MouseOrder] mo
JOIN 
    [dbo].[Customer] cus ON cus.Customer_ID = mo.CustomerId
JOIN 
    [dbo].[InventoryOrder] io ON io.OrderId = mo.MouseOrder_ID
JOIN 
    [dbo].[Inventory] i ON i.Inventory_ID =  io.InventoryId 
WHERE 
    mo.OrderDate = CONVERT(date, GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') 
    AND mo.SAMAccountEmail = '[email protected]'

Which I tried to do as a stored procedure so it can generate the HTML response

CREATE PROCEDURE GenerateEmailBody
AS
BEGIN
    DECLARE @xhtmlBody XML,
            @body NVARCHAR(MAX),
            @tableCaption VARCHAR(30) = 'Orderlist';

    SET @xhtmlBody = (SELECT (
SELECT cus.CustomerNumber as CustomerNumber,cus.Location as ReceivingLocation, 
       i.StrainName as StrainName,i.StrainCode as StrainCode,i.Age as Age,
       i.Sex as Sex,i.Genotype as Genotype,i.RoomNumber as SentFrom,io.OrderQuantity as OrderQuantity
FROM [dbo].[MouseOrder] mo
JOIN [dbo].[Customer] cus on cus.Customer_ID = mo.CustomerId
JOIN [dbo].[InventoryOrder] io on io.OrderId = mo.MouseOrder_ID
JOIN [dbo].[Inventory] i on i.Inventory_ID =  io.InventoryId 
WHERE mo.OrderDate = convert(date,getdate() AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time') and mo.SAMAccountEmail = '[email protected]'
FOR XML PATH('row'), TYPE, ROOT('root'))
.query('<html><head>
            <meta charset="utf-8"/>
            (: including embedded CSS styling :)
            <style>
            table <![CDATA[ {border-collapse: collapse;  width: 300px;} ]]>
            th <![CDATA[ {background-color: #4CAF50; color: white;} ]]>
            th, td <![CDATA[ { text-align: left; padding: 8px;} ]]>
            tr:nth-child(even) <![CDATA[ {background-color: #f2f2f2;} ]]>
            #green <![CDATA[ {background-color: lightgreen;} ]]>
         </style>
         </head>
         <body>
<table border="1">
   <caption><h2>{sql:variable("@tableCaption")}</h2></caption>
   <thead>
      <tr>
        <th>Cust Numb</th>
        <th>Rec Location</th>
        <th>Strain</th>
        <th>Strain Code</th>
        <th>Age</th>
        <th>Sex</th>
        <th>Genotype</th>
        <th>Sent From</th>
        <th>Order Quantity</th>
      </tr>
   </thead>
   <tbody>
{
    for $row in /root/row
    return <tr>
            <td>{data($row/CustomerNumber)}</td>
            <td>{data($row/ReceivingLocation)}</td>
            <td>{data($row/StrainName)}</td>
            <td>{data($row/StrainCode)}</td>
            <td>{data($row/Age)}</td>
            <td>{data($row/Sex)}</td>
            <td>{data($row/Genotype)}</td>
            <td>{data($row/SentFrom)}</td>
            <td>{data($row/OrderQuantity)}</td>
        </tr>
}
</tbody></table></body></html>'));

SELECT @xhtmlBody;

SET @body = CAST(@xhtmlBody AS NVARCHAR(MAX));
END
GO

When I tried to execute the stored procedure, it returns the int as shown here:

This is my first stored procedure attempt - I'm not sure what I am missing - any help is greatly appreciated

****** EDIT ******

The Execute SQL Task

The Scipt is like below

namespace ST_c2e68b3edd6842c4a6554376987c97c1
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        }

        public void Main()
        {
            string data = Dts.Variables["User::EmailData"].Value.ToString();
            SendEmail(data);
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        private void SendEmail(string messageBody)
        {
            ConnectionManager smtpConnectionManager = Dts.Connections["SMTP Connection Manager"];
            SmtpClient emailClient = new SmtpClient(smtpConnectionManager.Properties["SmtpServer"].GetValue(smtpConnectionManager).ToString());

            MailMessage email = new MailMessage();
            email.Priority = MailPriority.Normal;
            email.IsBodyHtml = true; 
            email.From = new MailAddress("[email protected]");
            email.To.Add("[email protected]"); 
            email.Body = messageBody;
            emailClient.Send(email);
  }    }}

It throws error like

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "EXEC dbo.GenerateEmailBody ?" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Execute SQL Task

CodePudding user response：

Assuming you want @body, you could make that an output parameter:

CREATE PROCEDURE GenerateEmailBody (@body NVARCHAR(MAX) OUTPUT)
AS
BEGIN
DECLARE @xhtmlBody XML
      , @tableCaption VARCHAR(30) = 'Orderlist';
...

and then call it like:

DECLARE @return_value int
DECLARE @html NVARCHAR(MAX)

exec @return_value = [dbo].[GenerateEmailBody] @html OUTPUT
select @html

CodePudding user response：

Like below:

SQL

CREATE PROCEDURE dbo.usp_GenerateEmailBody
(
   @SAMAccountEmail VARCHAR(100)
)
AS
BEGIN
...
... and mo.SAMAccountEmail = @SAMAccountEmail -- usage of the SP parameter