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

redouardAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
---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)
0
redouardAuthor Commented:
Hi, that didn't work :(
0
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
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.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

redouardAuthor Commented:
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
'.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
why not:
 exec master.dbo.xp_sendmail    
            @recipients = @recipients,
            @copy_recipients = @copy_recipients,
            @message = @msg,      
            @query = @SQLString,
            @Subject = @Subj,
            @width = 800,
            @no_header = True

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
the last item (no header) also is incorrect
Do you want just the word "True" or do you also want the semicolon?

@no_header = True;'
my guess is it sb
@no_header = 'True'
0
redouardAuthor Commented:
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

0
redouardAuthor Commented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.