Send Out of Stock Notice

this will email staff about item that are out of stock. I added a where clause to define what out of stock was. mostly like for most out of stock would be qty of zero

USE [MHTRN]
GO
/****** Object:  StoredProcedure [dbo].[cp_SendEmails_Out_Of_Stock]    Script Date: 2/12/2018 8:16:28 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[cp_SendEmails_Out_Of_Stock] 
AS 
    

DECLARE @body varchar(max)
DECLARE @subject varchar(max)
DECLARE @tableHTML varchar(max)
DECLARE @Table VARCHAR(MAX) = N''

SET @subject = 'Daily Out Of Stock Notice'

SELECT @Table = @Table +'<tr>' +
'<td>' + i.Item_Number + '</td>' +
'<td>' + i.Item_Description + '</td>' +
'<td>' + xStockStatus+ '</td>' +
'<td>' + CONVERT(VARCHAR(30),Avail) + '</td>' +
'<td>' + CONVERT(VARCHAR(30),Onorder) + '</td>' +
'<td>' + CONVERT(VARCHAR(30),Onhand) + '</td>' +
'</tr>'


FROM spvItemMaster as im 
left join spvInventory as i on i.Item_Number = im.Item_Number
 where Avail < 1 and Onorder < 1
 and xStockStatus like 'Quick Ship'


 


SET @tableHTML = 
N'<H3><font color="Black">Out Of Stock Notice</H3>' +
N'<table border="1" align="center" cellpadding="2" cellspacing="0" style="color:Black;font-family:arial,helvetica,sans-serif;text-align:center;" >' +
N'<tr style ="font-size: 14px;font-weight: normal;background: #b9c9fe;">
<th>Item Number</th>
<th>Item Description</th>
<th>Stock Status</th>
<th>Avail</th>
<th>On Order</th>
<th>On Hand</th>
</tr>' + @Table + N'</table>' 

SET @body = '<!doctype html> <html> <head> <meta charset="utf-8"> <title>Out Of Stock Notice</title> </head> <body> <p>Dear Staff, </p> <p>Please note the following Items are out of stack as of the time on this e-mail</p>' + @tableHTML + '<p>Have a great day!</p> <p>Purchasing Dept<br>   Jorgenson Industiral Companies<br>     Purchasing@jorgensoncompanies.com<br>   1239 South 700 West<br>   Salt Lake City, UT  84104<br> </p></body> </html>' 


EXEC msdb.dbo.sp_send_dbmail @recipients='jason@jorgensoncompanies.com',
@subject = @subject,
@body = @body,
@profile_name = 'Purch Send',
@body_format = 'HTML' ;


    RETURN;