Creating a stored procedure but how should i add a checkcount-CodePudding

I am creating an SPX and want to add a check count that if record exists from both queries, it should send an email else no email.

What am I missing here in my code? Please guide

create procedure [dbo].[checkdata] 
as
    set nocount on

    @SQLQuery1 = select * from employees where _date > dateadd(day,-2,getdate()) and loginId is null;

    @SQLQuery2 = select * from employees where _date > dateadd(day,-2,getdate()) and LoginID not in (select name from table2 where is_disabled = 0);

    if check to be added here for both queries
    begin
        EXEC msdb.dbo.sp_send_dbmail
            @recipients = '[email protected]', 
            @body = @SQLQuery1,
            @subject = 'data send'
end

CodePudding user response：

drop procedure if exists [dbo].[checkdata];
go 

create procedure [dbo].[checkdata]
as
 begin
   declare @InFlag INT=0;
   declare @InFlag2 INT=0;

   if(exists(select 1 from employees 
              where _date > dateadd(day,-2,getdate()) and   loginId is null
       ))
    set @InFlag=1;
  
   if(exists(select 1 from employees 
             where _date > dateadd(day,-2,getdate()) 
             and LoginID not in (select name from table2 where is_disabled = 0)
           ))
    set @InFlag2=1;
   
   if(@InFlag=1 and @InFlag=2)
    begin
     --send an email
    end
  
end--END PROCEDURE
go

CodePudding user response：

It looks like you should be able to do:

if exists (
  select * 
  from employees 
  where _date > dateadd(day,-2,getdate()) 
    and (
      loginId is null 
      or LoginID not in (select [name] from table2 where is_disabled = 0)
    )
)
begin
...
end

CodePudding user response：

This should help

create proc [dbo].[checkdata] as
begin
    set nocount on;

    declare @body nvarchar(max) = N'
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <style>
        body {
            font-family: Ubuntu;
            padding: 10px;
            margin: 10px;
        }
        caption {
            text-align: left;
            font-size: 180%;
            font-weight: normal;
            padding: 10px;
            margin-bottom: 20px;
        }
        table thead th {
            background-color: transparent; 
            font-size: 100%;
            text-align: left;
            font-weight: bold;
        }
        table tbody td {
            padding: 2px;
            border: 0 none;
            border-top: 1px solid silver;
        }
    </style>
    </head>
    <body>
    <font face="Calibri">
    some cool text
    <br>
    </font>
    <table><caption>query result</caption>
    ';

    -- headers
    set @body = @body   cast ((
    select col1 [th]
    , col2 [th]
    , col3 [th]
    , col4 [th]
    from (
        select 'val1', 'val2', 'val3', 'val4'
    ) as fake_data (col1, col2, col3, col4)
    for xml raw('tr'), elements, root('thead')
    ) as nvarchar(max));

    set @body = @body   cast ((
    select col1 [td]
    , col2 [td]
    , col3 [td]
    , col4 [td]
    from (
                  select N'name1', 'code1'  ,'value1'   ,'number1'
        union all select N'name2', 'code2'  ,'value2'   ,'number2'
        union all select N'name3', 'code3'  ,'value3'   ,'number3'
        union all select N'name4', 'code4'  ,'value4'   ,'number4'
        union all select N'name5', 'code5'  ,'value5'   ,'number5'
        union all select N'name6', 'code6'  ,'value6'   ,'number6'
    ) as fake_data(col1, col2, col3, col4)
    for xml raw('tr'), elements, root('tbody')
    ) as nvarchar(max));

    if exists(select * from employees where blablabla = blablabla)
    begin  
        EXEC msdb.dbo.sp_send_dbmail  
        @profile_name = 'default', 
        @recipients = '[email protected]',
        @subject = 'data send',
        @body_format = 'HTML',
        @body = @body;
    end
end

The letter