How to create sql server report for mail recepient?

Once its work please accept my answer  :) happy coding....

Hi Jithu

Thanks for your responce, let me work on this will give an update. This should be enough for me to test and write. 

Configure Database Mail - reference: - https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/configure-database-mail?view=sql-server-ver16

Modify your query to check for QtyOnHand = 0 and format the results for the email.

DECLARE @body NVARCHAR(MAX);

-- Prepare the email body with the list of items having Stock on Hand = 0
SET @body = N'
Stock Alert: Items with zero stock on hand
';

-- Add the rows to the email body
SELECT @body = @body + N''
FROM sysproR.dbo.InvMaster InvMaster
JOIN sysproR.dbo.InvWarehouse InvWarehouse
ON InvMaster.StockCode = InvWarehouse.StockCode
WHERE InvWarehouse.QtyOnHand = 0;

-- Close the HTML tags
SET @body = @body + N'

               

                 
StockCode
                 
Description
                 
Warehouse
                 
QtyOnHand
                 
QtyAllocated
                 
QtyOnOrder
                 
QtyInTransit
                 
QtyOnBackOrder
               

                         
' + InvWarehouse.StockCode + N'
                         
' + InvMaster.Description + N'
                         
' + InvWarehouse.Warehouse + N'
                         
' + CAST(InvWarehouse.QtyOnHand AS NVARCHAR) + N'
                         
' + CAST(InvWarehouse.QtyAllocated AS NVARCHAR) + N'
                         
' + CAST(InvWarehouse.QtyOnOrder AS NVARCHAR) + N'
                         
' + CAST(InvWarehouse.QtyInTransit AS NVARCHAR) + N'
                         
' + CAST(InvWarehouse.QtyOnBackOrder AS NVARCHAR) + N'
                         
';

-- Check if there are items with zero stock on hand
IF EXISTS (
    SELECT 1
    FROM sysproR.dbo.InvWarehouse
    WHERE QtyOnHand = 0
)
BEGIN
    -- Send the email
    EXEC msdb.dbo.sp_send_dbmail
         @profile_name = 'YourMailProfile', -- Replace with your Database Mail profile name
         @recipients = '[email protected]', -- Replace with the recipient email address
         @subject = 'Stock Alert: Items with zero stock on hand',
         @body = @body,
         @body_format = 'HTML';
END

Create a SQL Server Agent Job:

• Open SQL Server Management Studio (SSMS).
• In Object Explorer, expand the SQL Server Agent node.
• Right-click Jobs and select New Job.
• In the New Job dialog, enter the job name and description.
• Go to the Steps page, click New, and add a new step:
  • Step Name: Check Stock Levels
  • Type: Transact-SQL script (T-SQL)
  • Database: Select the database where your tables are located.
  • Command: Paste the above SQL query.
• Go to the Schedules page and create a new schedule for how often you want this check to run (e.g., daily at a specific time).