Link to home
Start Free TrialLog in
Avatar of redouard
redouard

asked on

Incorrect syntax near '@execstring'.

Hello,

I'm getting the following error message: "Line 230: Incorrect syntax near '@execstring'." and I don't know why and how to fix it. Please advised

IF OBJECT_ID('dbo.[sp_ACH_MissingChecks_dev]') IS NOT NULL
 BEGIN
      DROP PROCEDURE dbo.[sp_ACH_MissingChecks_dev]

 IF OBJECT_ID('dbo.[sp_ACH_MissingChecks_dev]') IS NOT NULL
      PRINT '<<< FAILED DROPPING PROCEDURE dbo.[sp_ACH_MissingChecks_dev] >>>'
            ELSE
      PRINT '<<< DROPPED PROCEDURE dbo.[sp_ACH_MissingChecks_dev] >>>'
            END
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.[sp_ACH_MissingChecks_dev]
AS

BEGIN
------------------Begin of Cursor------------------
DECLARE
      @CHECK_NUM VARCHAR(50)

DECLARE CurrData CURSOR
FOR
SELECT DISTINCT AC.CHECK_NUM
 
            FROM CMSOPEN.DBO.APT_CHECK AC LEFT OUTER JOIN CMSOPEN.DBO._TBL_ACH_EMAIL T
            ON AC.CHECK_NUM = T.CHECK_NUM

            INNER JOIN CMSOPEN.DBO.APT_INVOICE_AMT AP
            ON AC.TRAN_UNO = AP.SOURCE_TRAN_UNO

            INNER JOIN CMSOPEN.DBO.APT_INVOICE API
            ON AP.INV_TRAN_UNO = API.TRAN_UNO

            INNER JOIN CMSOPEN.DBO.APM_VENDOR APV
            ON APV.VENDOR_UNO = AC.VENDOR_UNO

            WHERE AC.CHECK_NUM NOT IN (SELECT CHECK_NUM FROM CMSOPEN.DBO._TBL_ACH_EMAIL)
            AND AC.BANK_CODE = 'ACH'
            AND AC._ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111)

OPEN CurrData
FETCH NEXT FROM CurrData
INTO @CHECK_NUM
------------------End of cursor-------------------
WHILE @@FETCH_STATUS = 0
 BEGIN

DECLARE
      @Count_CHECK_NUM VARCHAR(50),
      @Count_SCHEDULE_NUM VARCHAR(50),
      @Count_EMAIL_ADDR VARCHAR(50),
      
      @SQLString varchar(8000),
      @recipients VARCHAR(200),
      @copy_recipients VARCHAR(200),
      @Msg VARCHAR(600),
      @Subj VARCHAR(500),
      @execstring VARCHAR(8000)

SET @Count_SCHEDULE_NUM = (SELECT max(schedule_num)
FROM dbo.apt_check WHERE _ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111))

SET @Count_CHECK_NUM = (SELECT COUNT(check_num)
FROM dbo.apt_check WHERE _ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111))

SET @Count_EMAIL_ADDR = (SELECT COUNT (DISTINCT EMAIL_ADDR)
FROM dbo._tbl_ACH_Email WHERE _ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111))

IF (SELECT COUNT(*) FROM CMSOPEN.DBO._tbl_ACH_Email WHERE _ACH_EFF_DATE = CONVERT(CHAR(10),GETDATE(),101)) = 0
Begin
      DECLARE      @EmailBody VARCHAR(500),
            @EmailSubjlne VARCHAR(500)
            
      SELECT
      @EmailSubjlne = 'ACH Direct Deposit Notification'+space(1)+CONVERT(CHAR(10),GETDATE(),101),
      @EmailBody = 'Schedule_Num = 0'
      +char(10)+'Total # of Checks = 0'
      +char(10)+'Total # of Emails = 0'+char(10)+space(1)
      +char(10)+'There are no ACH Direct Deposit transactions for'+space(1)+CONVERT(CHAR(10),GETDATE(),101)
            
            exec master.dbo.xp_sendmail
            @recipients = 'ruben.edouard@weil.com',
            @message = @EmailBody,
            @Subject = @EmailSubjlne;
END
       ELSE
--Check for missing checks count
IF (SELECT COUNT(AC.CHECK_NUM)
 
            FROM CMSOPEN.DBO.APT_CHECK AC LEFT OUTER JOIN CMSOPEN.DBO._TBL_ACH_EMAIL T
            ON AC.CHECK_NUM = T.CHECK_NUM

            INNER JOIN CMSOPEN.DBO.APT_INVOICE_AMT AP
            ON AC.TRAN_UNO = AP.SOURCE_TRAN_UNO

            INNER JOIN CMSOPEN.DBO.APT_INVOICE API
            ON AP.INV_TRAN_UNO = API.TRAN_UNO

            INNER JOIN CMSOPEN.DBO.APM_VENDOR APV
            ON APV.VENDOR_UNO = AC.VENDOR_UNO

            WHERE AC.CHECK_NUM NOT IN (SELECT CHECK_NUM FROM CMSOPEN.DBO._TBL_ACH_EMAIL)
            AND AC.BANK_CODE = 'ACH'
            AND AC._ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111))= 0
      
BEGIN
      DECLARE            @Body VARCHAR(500),
                  @Subjlne VARCHAR(500)
      
      SELECT
                  @Subjlne = 'Noreply - Missing ACH Checks E-mail Notification.',
                  @Body = 'Schedule_Num ='+space(1)+@count_schedule_num
                  +char(10)+'Total # of Checks ='+space(1)+@count_check_num
                  +char(10)+'Total # of Emails ='+space(1)+@count_email_addr+char(10)
                  +char(10)+'E-mail notifications were submitted successfully for all issued checks for'+space(1)+CONVERT(CHAR(10),GETDATE(),101)
            
            exec master.dbo.xp_sendmail
                  @recipients = 'ruben.edouard@weil.com',
                  @message = @Body,
                  @Subject = @Subjlne;
END
      ELSE

---Format email notification output
SELECT
      @recipients = 'ruben.edouard@weil.com',
      @copy_recipients = 'ruben.edouard@weil.com',
      @Subj = 'Noreply - Missing ACH Checks E-mail Notification.',
      @Msg = 'Schedule_Num ='+space(1)+@count_schedule_num
      +char(10)+'Total # of Checks ='+space(1)+@count_check_num
      +char(10)+'Total # of Emails ='+space(1)+@count_email_addr+char(10)
      +char(10)+'Although check was issued, e-mail notification has not been sent for the following check(s):',
            
      @SQLString = 'IF(SELECT COUNT(AC.CHECK_NUM)

       FROM CMSOPEN.DBO.APT_CHECK AC LEFT OUTER JOIN CMSOPEN.DBO._TBL_ACH_EMAIL T
      ON AC.CHECK_NUM = T.CHECK_NUM
      
      INNER JOIN CMSOPEN.DBO.APT_INVOICE_AMT AP
      ON AC.TRAN_UNO = AP.SOURCE_TRAN_UNO
      
      INNER JOIN CMSOPEN.DBO.APT_INVOICE API
      ON AP.INV_TRAN_UNO = API.TRAN_UNO
      
      INNER JOIN CMSOPEN.DBO.APM_VENDOR APV
      ON APV.VENDOR_UNO = AC.VENDOR_UNO
      
      WHERE AC.CHECK_NUM NOT IN (SELECT CHECK_NUM FROM CMSOPEN.DBO._TBL_ACH_EMAIL)
      AND AC.BANK_CODE = ''''ACH''''
      AND AC._ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111)
      AND AC.CHECK_NUM = '''''+@check_num+''''') > 1
BEGIN
      SELECT DISTINCT + char(10)+''''-----------''''+char(10)+''''SCHEDULE_NUM#:''''+ space(1)+ CONVERT(VARCHAR(50),AC.SCHEDULE_NUM)
      +char(10)+''''VENDOR_NAME:''''+ space(1)+CONVERT(VARCHAR(50),APV.VENDOR_NAME)
      +char(10)+''''CHECK_NUM#:''''+ space(1)+ CONVERT(VARCHAR(50),AC.CHECK_NUM)
      +char(10)+''''CHECK_AMT:''''+ space(1)+''''$''''+CONVERT(VARCHAR(10),AC.CHK_AMT)

      FROM CMSOPEN.DBO.APT_CHECK AC LEFT OUTER JOIN CMSOPEN.DBO._TBL_ACH_EMAIL T
      ON AC.CHECK_NUM = T.CHECK_NUM
      
      INNER JOIN CMSOPEN.DBO.APT_INVOICE_AMT AP
      ON AC.TRAN_UNO = AP.SOURCE_TRAN_UNO
      
      INNER JOIN CMSOPEN.DBO.APT_INVOICE API
      ON AP.INV_TRAN_UNO = API.TRAN_UNO
      
      INNER JOIN CMSOPEN.DBO.APM_VENDOR APV
      ON APV.VENDOR_UNO = AC.VENDOR_UNO

      WHERE AC.CHECK_NUM NOT IN (SELECT CHECK_NUM FROM CMSOPEN.DBO._TBL_ACH_EMAIL)
      AND AC.BANK_CODE = ''''ACH''''
      AND AC._ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111)
      AND AC.CHECK_NUM = '''''+@check_num+'''''
                  
         SELECT char(10)+''''INVOICE_NUM:''''+ space(1)+ API.INVOICE_NUM
      +char(10)+''''INV_AMT:''''+ space(1)+''''$''''+CONVERT(VARCHAR (10),API.INV_AMT)

         FROM CMSOPEN.DBO.APT_CHECK AC LEFT OUTER JOIN CMSOPEN.DBO._TBL_ACH_EMAIL T
      ON AC.CHECK_NUM = T.CHECK_NUM

      INNER JOIN CMSOPEN.DBO.APT_INVOICE_AMT AP
      ON AC.TRAN_UNO = AP.SOURCE_TRAN_UNO
      INNER JOIN CMSOPEN.DBO.APT_INVOICE API
      ON AP.INV_TRAN_UNO = API.TRAN_UNO
      INNER JOIN CMSOPEN.DBO.APM_VENDOR APV
      ON APV.VENDOR_UNO = AC.VENDOR_UNO

      WHERE AC.CHECK_NUM NOT IN (SELECT CHECK_NUM FROM CMSOPEN.DBO._TBL_ACH_EMAIL)
      AND AC.BANK_CODE = ''''ACH''''
      AND AC._ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111)
      AND AC.CHECK_NUM = '''''+@check_num+'''''
END
ELSE
      IF (SELECT COUNT (AC.CHECK_NUM)

       FROM CMSOPEN.DBO.APT_CHECK AC LEFT OUTER JOIN CMSOPEN.DBO._TBL_ACH_EMAIL T
      ON AC.CHECK_NUM = T.CHECK_NUM
      
      INNER JOIN CMSOPEN.DBO.APT_INVOICE_AMT AP
      ON AC.TRAN_UNO = AP.SOURCE_TRAN_UNO
      
      INNER JOIN CMSOPEN.DBO.APT_INVOICE API
      ON AP.INV_TRAN_UNO = API.TRAN_UNO
      
      INNER JOIN CMSOPEN.DBO.APM_VENDOR APV
      ON APV.VENDOR_UNO = AC.VENDOR_UNO
      
      WHERE AC.CHECK_NUM NOT IN (SELECT CHECK_NUM FROM CMSOPEN.DBO._TBL_ACH_EMAIL)
      AND AC.BANK_CODE = ''''ACH''''
      AND AC._ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111)
      AND AC.CHECK_NUM = '''''+@check_num+''''')= 1
BEGIN
      SELECT DISTINCT + char(10)+''''-----------''''+char(10)+''''SCHEDULE_NUM#:''''+ space(1)+ CONVERT(VARCHAR(10),AC.SCHEDULE_NUM)
      +char(10)+''''VENDOR_NAME:''''+ space(1)+CONVERT(VARCHAR(50),APV.VENDOR_NAME)
      +char(10)+''''CHECK_NUM#:''''+ space(1)+ CONVERT(VARCHAR(50),AC.CHECK_NUM)
      +char(10)+''''CHECK_AMT:''''+ space(1)+''''$''''+CONVERT(VARCHAR(50),AC.CHK_AMT)
      +char(10)+''''INVOICE_NUM:''''+ space(1)+ API.INVOICE_NUM
      +char(10)+''''INV_AMT:''''+ space(1)+''''$''''+CONVERT(VARCHAR (50),API.INV_AMT)

      FROM CMSOPEN.DBO.APT_CHECK AC LEFT OUTER JOIN CMSOPEN.DBO._TBL_ACH_EMAIL T
      ON AC.CHECK_NUM = T.CHECK_NUM
      
      INNER JOIN CMSOPEN.DBO.APT_INVOICE_AMT AP
      ON AC.TRAN_UNO = AP.SOURCE_TRAN_UNO
      
      INNER JOIN CMSOPEN.DBO.APT_INVOICE API
      ON AP.INV_TRAN_UNO = API.TRAN_UNO
      
      INNER JOIN CMSOPEN.DBO.APM_VENDOR APV
      ON APV.VENDOR_UNO = AC.VENDOR_UNO

      WHERE AC.CHECK_NUM NOT IN (SELECT CHECK_NUM FROM CMSOPEN.DBO._TBL_ACH_EMAIL)
      AND AC.BANK_CODE = ''''ACH''''
      AND AC._ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111)
      AND AC.CHECK_NUM = '''''+@check_num+'''''
END'

FETCH NEXT FROM CurrData
      INTO @check_num

---Send email notification
      @execstring = 'master.dbo.xp_sendmail
            @recipients = '''+@recipients+''',
            @copy_recipients = '''+@copy_recipients+''',
            @message = '''+@msg+''',      
            @query = '''+@SQLString+''',
            @Subject = '''+@Subj+''',
            @width = 800,
            @no_header = True;'

      --EXEC(@execstring)
      PRINT(@execstring)

END

      CLOSE CurrData
      DEALLOCATE CurrData
END
GO

Avatar of Daniel Reynolds
Daniel Reynolds
Flag of United States of America image
---Send email notification
      @execstring = 'master.dbo.xp_sendmail    <<<----Add an apostrophe here?
            @recipients = '''+@recipients+''',
            @copy_recipients = '''+@copy_recipients+''',
            @message = '''+@msg+''',      
            @query = '''+@SQLString+''',
            @Subject = '''+@Subj+''',
            @width = 800,
            @no_header = True;'

      --EXEC(@execstring)
      PRINT(@execstring)
Avatar of redouard
redouard

ASKER

Hi, that didn't work :(
Avatar of Daniel Reynolds
Daniel Reynolds
Flag of United States of America image
did you include a space?

@execstring = 'master.dbo.xp_sendmail '    <<<----Add an apostrophe here?

print out the execstring and post it here for us to see.
Avatar of redouard
redouard

ASKER

Server: Msg 170, Level 15, State 1, Procedure sp_ACH_MissingChecks_dev, Line 229
Line 229: Incorrect syntax near '@recipients'.
Server: Msg 105, Level 15, State 1, Procedure sp_ACH_MissingChecks_dev, Line 235
Unclosed quotation mark before the character string '


      EXEC(@execstring)
      --PRINT(@execstring)
 END
      CLOSE CurrData
      DEALLOCATE CurrData

END
'.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Daniel Reynolds
Daniel Reynolds
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of redouard
redouard

ASKER

Hello Everyone,

My code is not working. I concluded that I need to rewrite the stored procedure. How and what do I need to do to change the attached stored procedure to send one email containing all the results from the select statement instead of sending one email for each records it finds. Any help is appreciated.
IF OBJECT_ID('dbo.[sp_ACH_MissingChecks_dev]') IS NOT NULL
 BEGIN
	DROP PROCEDURE dbo.[sp_ACH_MissingChecks_dev]
 
 IF OBJECT_ID('dbo.[sp_ACH_MissingChecks_dev]') IS NOT NULL
	PRINT '<<< FAILED DROPPING PROCEDURE dbo.[sp_ACH_MissingChecks_dev] >>>'
		ELSE
	PRINT '<<< DROPPED PROCEDURE dbo.[sp_ACH_MissingChecks_dev] >>>'
		END
GO
 
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE PROCEDURE dbo.[sp_ACH_MissingChecks_dev]
AS
 
BEGIN 
------------------Begin of Cursor------------------
DECLARE
	@CHECK_NUM VARCHAR(50)
 
DECLARE CurrData CURSOR 
FOR
SELECT DISTINCT AC.CHECK_NUM
 
		FROM CMSOPEN.DBO.APT_CHECK AC LEFT OUTER JOIN CMSOPEN.DBO._TBL_ACH_EMAIL T 
		ON AC.CHECK_NUM = T.CHECK_NUM
 
		INNER JOIN CMSOPEN.DBO.APT_INVOICE_AMT AP
		ON AC.TRAN_UNO = AP.SOURCE_TRAN_UNO
 
		INNER JOIN CMSOPEN.DBO.APT_INVOICE API
		ON AP.INV_TRAN_UNO = API.TRAN_UNO
 
		INNER JOIN CMSOPEN.DBO.APM_VENDOR APV
		ON APV.VENDOR_UNO = AC.VENDOR_UNO
 
		WHERE AC.CHECK_NUM NOT IN (SELECT CHECK_NUM FROM CMSOPEN.DBO._TBL_ACH_EMAIL)
		AND AC.BANK_CODE = 'ACH'
		AND AC._ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111)
 
OPEN CurrData
FETCH NEXT FROM CurrData
INTO @CHECK_NUM
------------------End of cursor-------------------
WHILE @@FETCH_STATUS = 0
 BEGIN 
 
DECLARE
	@Count_CHECK_NUM VARCHAR(50),
	@Count_SCHEDULE_NUM VARCHAR(50),
	@Count_EMAIL_ADDR VARCHAR(50),
	
	@SQLString varchar(8000),
	@recipients VARCHAR(200),
	@copy_recipients VARCHAR(200),
	@Msg VARCHAR(600),
	@Subj VARCHAR(500),
	@execstring VARCHAR(8000)
 
SET @Count_SCHEDULE_NUM = (SELECT max(schedule_num)
FROM dbo.apt_check WHERE _ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111))
 
SET @Count_CHECK_NUM = (SELECT COUNT(check_num)
FROM dbo.apt_check WHERE _ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111))
 
SET @Count_EMAIL_ADDR = (SELECT COUNT (DISTINCT EMAIL_ADDR)
FROM dbo._tbl_ACH_Email WHERE _ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111))
 
IF (SELECT COUNT(*) FROM CMSOPEN.DBO._tbl_ACH_Email WHERE _ACH_EFF_DATE = CONVERT(CHAR(10),GETDATE(),101)) = 0
Begin
	DECLARE	@EmailBody VARCHAR(500),
		@EmailSubjlne VARCHAR(500)
		
	SELECT
	@EmailSubjlne = 'ACH Direct Deposit Notification'+space(1)+CONVERT(CHAR(10),GETDATE(),101),
	@EmailBody = 'Schedule_Num = 0'
	+char(10)+'Total # of Checks = 0'
	+char(10)+'Total # of Emails = 0'+char(10)+space(1)
	+char(10)+'There are no ACH Direct Deposit transactions for'+space(1)+CONVERT(CHAR(10),GETDATE(),101)
		
		exec master.dbo.xp_sendmail
		@recipients = 'ruben.edouard@weil.com',
		@message = @EmailBody,
		@Subject = @EmailSubjlne;
END
 	ELSE
--Check for missing checks count
IF (SELECT COUNT(AC.CHECK_NUM)
 
		FROM CMSOPEN.DBO.APT_CHECK AC LEFT OUTER JOIN CMSOPEN.DBO._TBL_ACH_EMAIL T 
		ON AC.CHECK_NUM = T.CHECK_NUM
 
		INNER JOIN CMSOPEN.DBO.APT_INVOICE_AMT AP
		ON AC.TRAN_UNO = AP.SOURCE_TRAN_UNO
 
		INNER JOIN CMSOPEN.DBO.APT_INVOICE API
		ON AP.INV_TRAN_UNO = API.TRAN_UNO
 
		INNER JOIN CMSOPEN.DBO.APM_VENDOR APV
		ON APV.VENDOR_UNO = AC.VENDOR_UNO
 
		WHERE AC.CHECK_NUM NOT IN (SELECT CHECK_NUM FROM CMSOPEN.DBO._TBL_ACH_EMAIL)
		AND AC.BANK_CODE = 'ACH'
		AND AC._ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111))= 0
	
BEGIN
	DECLARE		@Body VARCHAR(500),
			@Subjlne VARCHAR(500)
	
	SELECT
			@Subjlne = 'Noreply - Missing ACH Checks E-mail Notification.',
			@Body = 'Schedule_Num ='+space(1)+@count_schedule_num
			+char(10)+'Total # of Checks ='+space(1)+@count_check_num
			+char(10)+'Total # of Emails ='+space(1)+@count_email_addr+char(10)
			+char(10)+'E-mail notifications were submitted successfully for all issued checks for'+space(1)+CONVERT(CHAR(10),GETDATE(),101)
		
		exec master.dbo.xp_sendmail
			@recipients = 'ruben.edouard@weil.com',
			@message = @Body,
			@Subject = @Subjlne;
END
	ELSE
 
---Format email notification output
SELECT
	@recipients = 'ruben.edouard@weil.com',
	@copy_recipients = 'ruben.edouard@weil.com',
	@Subj = 'Noreply - Missing ACH Checks E-mail Notification.',
	@Msg = 'Schedule_Num ='+space(1)+@count_schedule_num
	+char(10)+'Total # of Checks ='+space(1)+@count_check_num
	+char(10)+'Total # of Emails ='+space(1)+@count_email_addr+char(10)
	+char(10)+'Although check was issued, e-mail notification has not been sent for the following check(s):',
		
	@SQLString = 'IF(SELECT COUNT(AC.CHECK_NUM)
 
 	FROM CMSOPEN.DBO.APT_CHECK AC LEFT OUTER JOIN CMSOPEN.DBO._TBL_ACH_EMAIL T 
	ON AC.CHECK_NUM = T.CHECK_NUM
	
	INNER JOIN CMSOPEN.DBO.APT_INVOICE_AMT AP
	ON AC.TRAN_UNO = AP.SOURCE_TRAN_UNO
	
	INNER JOIN CMSOPEN.DBO.APT_INVOICE API
	ON AP.INV_TRAN_UNO = API.TRAN_UNO
	
	INNER JOIN CMSOPEN.DBO.APM_VENDOR APV
	ON APV.VENDOR_UNO = AC.VENDOR_UNO
	
	WHERE AC.CHECK_NUM NOT IN (SELECT CHECK_NUM FROM CMSOPEN.DBO._TBL_ACH_EMAIL)
	AND AC.BANK_CODE = ''''ACH''''
	AND AC._ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111)
	AND AC.CHECK_NUM = '''''+@check_num+''''') > 1
BEGIN
	SELECT DISTINCT + char(10)+''''-----------''''+char(10)+''''SCHEDULE_NUM#:''''+ space(1)+ CONVERT(VARCHAR(50),AC.SCHEDULE_NUM)
	+char(10)+''''VENDOR_NAME:''''+ space(1)+CONVERT(VARCHAR(50),APV.VENDOR_NAME)
	+char(10)+''''CHECK_NUM#:''''+ space(1)+ CONVERT(VARCHAR(50),AC.CHECK_NUM)
	+char(10)+''''CHECK_AMT:''''+ space(1)+''''$''''+CONVERT(VARCHAR(10),AC.CHK_AMT)
 
	FROM CMSOPEN.DBO.APT_CHECK AC LEFT OUTER JOIN CMSOPEN.DBO._TBL_ACH_EMAIL T 
	ON AC.CHECK_NUM = T.CHECK_NUM
	
	INNER JOIN CMSOPEN.DBO.APT_INVOICE_AMT AP
	ON AC.TRAN_UNO = AP.SOURCE_TRAN_UNO
	
	INNER JOIN CMSOPEN.DBO.APT_INVOICE API
	ON AP.INV_TRAN_UNO = API.TRAN_UNO
	
	INNER JOIN CMSOPEN.DBO.APM_VENDOR APV
	ON APV.VENDOR_UNO = AC.VENDOR_UNO
 
	WHERE AC.CHECK_NUM NOT IN (SELECT CHECK_NUM FROM CMSOPEN.DBO._TBL_ACH_EMAIL)
	AND AC.BANK_CODE = ''''ACH''''
	AND AC._ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111)
	AND AC.CHECK_NUM = '''''+@check_num+'''''
			
   	SELECT char(10)+''''INVOICE_NUM:''''+ space(1)+ API.INVOICE_NUM
	+char(10)+''''INV_AMT:''''+ space(1)+''''$''''+CONVERT(VARCHAR (10),API.INV_AMT)
 
   	FROM CMSOPEN.DBO.APT_CHECK AC LEFT OUTER JOIN CMSOPEN.DBO._TBL_ACH_EMAIL T 
	ON AC.CHECK_NUM = T.CHECK_NUM
 
	INNER JOIN CMSOPEN.DBO.APT_INVOICE_AMT AP
	ON AC.TRAN_UNO = AP.SOURCE_TRAN_UNO
	INNER JOIN CMSOPEN.DBO.APT_INVOICE API
	ON AP.INV_TRAN_UNO = API.TRAN_UNO
	INNER JOIN CMSOPEN.DBO.APM_VENDOR APV
	ON APV.VENDOR_UNO = AC.VENDOR_UNO
 
	WHERE AC.CHECK_NUM NOT IN (SELECT CHECK_NUM FROM CMSOPEN.DBO._TBL_ACH_EMAIL)
	AND AC.BANK_CODE = ''''ACH''''
	AND AC._ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111)
	AND AC.CHECK_NUM = '''''+@check_num+'''''
END
ELSE 
	IF (SELECT COUNT (AC.CHECK_NUM)
 
 	FROM CMSOPEN.DBO.APT_CHECK AC LEFT OUTER JOIN CMSOPEN.DBO._TBL_ACH_EMAIL T 
	ON AC.CHECK_NUM = T.CHECK_NUM
	
	INNER JOIN CMSOPEN.DBO.APT_INVOICE_AMT AP
	ON AC.TRAN_UNO = AP.SOURCE_TRAN_UNO
	
	INNER JOIN CMSOPEN.DBO.APT_INVOICE API
	ON AP.INV_TRAN_UNO = API.TRAN_UNO
	
	INNER JOIN CMSOPEN.DBO.APM_VENDOR APV
	ON APV.VENDOR_UNO = AC.VENDOR_UNO
	
	WHERE AC.CHECK_NUM NOT IN (SELECT CHECK_NUM FROM CMSOPEN.DBO._TBL_ACH_EMAIL)
	AND AC.BANK_CODE = ''''ACH''''
	AND AC._ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111)
	AND AC.CHECK_NUM = '''''+@check_num+''''')= 1
BEGIN
	SELECT DISTINCT + char(10)+''''-----------''''+char(10)+''''SCHEDULE_NUM#:''''+ space(1)+ CONVERT(VARCHAR(10),AC.SCHEDULE_NUM)
	+char(10)+''''VENDOR_NAME:''''+ space(1)+CONVERT(VARCHAR(50),APV.VENDOR_NAME)
	+char(10)+''''CHECK_NUM#:''''+ space(1)+ CONVERT(VARCHAR(50),AC.CHECK_NUM)
	+char(10)+''''CHECK_AMT:''''+ space(1)+''''$''''+CONVERT(VARCHAR(50),AC.CHK_AMT)
	+char(10)+''''INVOICE_NUM:''''+ space(1)+ API.INVOICE_NUM
	+char(10)+''''INV_AMT:''''+ space(1)+''''$''''+CONVERT(VARCHAR (50),API.INV_AMT)
 
	FROM CMSOPEN.DBO.APT_CHECK AC LEFT OUTER JOIN CMSOPEN.DBO._TBL_ACH_EMAIL T 
	ON AC.CHECK_NUM = T.CHECK_NUM
	
	INNER JOIN CMSOPEN.DBO.APT_INVOICE_AMT AP
	ON AC.TRAN_UNO = AP.SOURCE_TRAN_UNO
	
	INNER JOIN CMSOPEN.DBO.APT_INVOICE API
	ON AP.INV_TRAN_UNO = API.TRAN_UNO
	
	INNER JOIN CMSOPEN.DBO.APM_VENDOR APV
	ON APV.VENDOR_UNO = AC.VENDOR_UNO
 
	WHERE AC.CHECK_NUM NOT IN (SELECT CHECK_NUM FROM CMSOPEN.DBO._TBL_ACH_EMAIL)
	AND AC.BANK_CODE = ''''ACH''''
	AND AC._ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111)
	AND AC.CHECK_NUM = '''''+@check_num+'''''
END'
	
FETCH NEXT FROM CurrData
	INTO @check_num
 
---Send email notification
	select @execstring = 'master.dbo.xp_sendmail
		@recipients = '''+@recipients+''',
		@copy_recipients = '''+@copy_recipients+''',
		@message = '''+@msg+''',	
		@query = '''+@SQLString+''',
		@Subject = '''+@Subj+''',
		@width = 800,
		@no_header = True;'
 
	--EXEC(@execstring)
	PRINT(@execstring)
 
END 
	CLOSE CurrData
	DEALLOCATE CurrData
		
END
GO

Open in new window

Avatar of redouard
redouard

ASKER

Hello Everyone,
 
I ended up rewriting the stored procedure. Here's the new code
CREATE PROCEDURE dbo.[sp_ACH_MissingChecks]
AS
BEGIN 
 
DECLARE
	@Count_CHECK_NUM VARCHAR(50),
	@Count_SCHEDULE_NUM VARCHAR(50),
	@Count_EMAIL_ADDR VARCHAR(50),
	@SQLString varchar(8000),
	@recipients VARCHAR(200),
	@copy_recipients VARCHAR(200),
	@Msg VARCHAR(600),
	@Subj VARCHAR(500),
	@execstring VARCHAR(8000)
 
	SET @Count_SCHEDULE_NUM = (SELECT max(schedule_num)
	FROM dbo.apt_check WHERE _ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111))
 
	SET @Count_CHECK_NUM = (SELECT COUNT(check_num)
	FROM dbo.apt_check WHERE _ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111))
 
	SET @Count_EMAIL_ADDR = (SELECT COUNT (DISTINCT EMAIL_ADDR)
	FROM dbo._tbl_ACH_Email WHERE _ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111))
 
IF (SELECT COUNT(*) FROM CMSOPEN.DBO._tbl_ACH_Email WHERE _ACH_EFF_DATE = CONVERT(CHAR(10),GETDATE(),101)) = 0
BEGIN
	DECLARE	@EmailBody VARCHAR(500),
		@EmailSubjlne VARCHAR(500)
		
	SELECT
	@EmailSubjlne = 'ACH Direct Deposit Notification'+space(1)+CONVERT(CHAR(10),GETDATE(),101),
	@EmailBody = 'Schedule_Num = 0'
	+char(10)+'Total # of Checks = 0'
	+char(10)+'Total # of Emails = 0'+char(10)+space(1)
	+char(10)+'There are no ACH Direct Deposit transactions for'+space(1)+CONVERT(CHAR(10),GETDATE(),101)
		
		exec master.dbo.xp_sendmail
		@recipients = 'ruben.edouard@weil.com;Matthew.Blake@weil.com;Inga.Boguslavsky@weil.com;Dee.Kampel@weil.com',
		@message = @EmailBody,
		@Subject = @EmailSubjlne;
END
 	ELSE
--Check for missing checks count
IF (SELECT COUNT(AC.CHECK_NUM)
 
		FROM CMSOPEN.DBO.APT_CHECK AC LEFT OUTER JOIN CMSOPEN.DBO._TBL_ACH_EMAIL T 
		ON AC.CHECK_NUM = T.CHECK_NUM
 
		INNER JOIN CMSOPEN.DBO.APT_INVOICE_AMT AP
		ON AC.TRAN_UNO = AP.SOURCE_TRAN_UNO
 
		INNER JOIN CMSOPEN.DBO.APT_INVOICE API
		ON AP.INV_TRAN_UNO = API.TRAN_UNO
 
		INNER JOIN CMSOPEN.DBO.APM_VENDOR APV
		ON APV.VENDOR_UNO = AC.VENDOR_UNO
 
		WHERE AC.CHECK_NUM NOT IN (SELECT CHECK_NUM FROM CMSOPEN.DBO._TBL_ACH_EMAIL)
		AND AC.BANK_CODE = 'ACH'
		AND AC._ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111))= 0
	
BEGIN
	DECLARE		@Body VARCHAR(500),
			@Subjlne VARCHAR(500)
	
	SELECT
			@Subjlne = 'Noreply - Missing ACH Checks E-mail Notification.',
			@Body = 'Schedule_Num ='+space(1)+@count_schedule_num
			+char(10)+'Total # of Checks ='+space(1)+@count_check_num
			+char(10)+'Total # of Emails ='+space(1)+@count_email_addr+char(10)
			+char(10)+'E-mail notifications were submitted successfully for all issued checks for'+space(1)+CONVERT(CHAR(10),GETDATE(),101)
		
		exec master.dbo.xp_sendmail
			@recipients = 'ruben.edouard@weil.com;Matthew.Blake@weil.com;Inga.Boguslavsky@weil.com;Dee.Kampel@weil.com',
			@message = @Body,
			@Subject = @Subjlne;
END
	ELSE
 
---Format email notification output
SELECT
	@recipients = 'Raymond.Ortiz@weil.com;Kathy.Ortiz@weil.com;Socorro.Germosen@weil.com;Desiree.Rhodes@weil.com',
	@copy_recipients = 'ruben.edouard@weil.com;Matthew.Blake@weil.com;Inga.Boguslavsky@weil.com;Dee.Kampel@weil.com',
	@Subj = 'Noreply - Missing ACH Checks E-mail Notification.',
	@Msg = 'Schedule_Num ='+space(1)+@count_schedule_num
	+char(10)+'Total # of Checks ='+space(1)+@count_check_num
	+char(10)+'Total # of Emails ='+space(1)+@count_email_addr+char(10)
	+char(10)+'Although check was issued, e-mail notification has not been sent for the following check(s):',
		
	@SQLString = 'SELECT DISTINCT + char(10)+''''-----------''''
	+char(10)+''''SCHEDULE_NUM#:''''+ space(1)+ CONVERT(VARCHAR(10),AC.SCHEDULE_NUM)
	+char(10)+''''VENDOR_NAME:''''+ space(1)+CONVERT(VARCHAR(50),APV.VENDOR_NAME)
	+char(10)+''''CHECK_NUM#:''''+ space(1)+ CONVERT(VARCHAR(50),AC.CHECK_NUM)
	+char(10)+''''CHECK_AMT:''''+ space(1)+''''$''''+CONVERT(VARCHAR(50),AC.CHK_AMT)
	+char(10)+''''INVOICE_NUM:''''+ space(1)+ API.INVOICE_NUM
	+char(10)+''''INV_AMT:''''+ space(1)+''''$''''+CONVERT(VARCHAR (50),API.INV_AMT)
 
	FROM CMSOPEN.DBO.APT_CHECK AC LEFT OUTER JOIN CMSOPEN.DBO._TBL_ACH_EMAIL T 
	ON AC.CHECK_NUM = T.CHECK_NUM
		INNER JOIN CMSOPEN.DBO.APT_INVOICE_AMT AP
	ON AC.TRAN_UNO = AP.SOURCE_TRAN_UNO
		INNER JOIN CMSOPEN.DBO.APT_INVOICE API
	ON AP.INV_TRAN_UNO = API.TRAN_UNO
		INNER JOIN CMSOPEN.DBO.APM_VENDOR APV
	ON APV.VENDOR_UNO = AC.VENDOR_UNO
 
	WHERE AC.CHECK_NUM NOT IN (SELECT CHECK_NUM FROM CMSOPEN.DBO._TBL_ACH_EMAIL)
	AND AC.BANK_CODE = ''''ACH''''
	AND AC._ACH_EFF_DATE = CONVERT(char(40),GETDATE(), 111)
	AND AC.CHECK_NUM IN (SELECT CHECK_NUM FROM CMSOPEN.DBO.APT_CHECK)',
 
---Send email notification
	@execstring = 'master.dbo.xp_sendmail
		@recipients = '''+@recipients+''',
		@copy_recipients = '''+@copy_recipients+''',
		@message = '''+@msg+''',	
		@query = '''+@SQLString+''',
		@Subject = '''+@Subj+''',
		@width = 800,
		@no_header = True;'
	EXEC(@execstring)
END
 
GO

Open in new window