?
Solved

Incorrect syntax near '@execstring'.

Posted on 2008-11-14
8
Medium Priority
?
463 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:redouard
  • 4
  • 3
8 Comments
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 22962276
---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
 

Author Comment

by:redouard
ID: 22962331
Hi, that didn't work :(
0
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 22962448
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:redouard
ID: 22963470
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 225 total points
ID: 22963588
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
 
LVL 17

Assisted Solution

by:Daniel Reynolds
Daniel Reynolds earned 150 total points
ID: 22963599
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
 

Author Comment

by:redouard
ID: 22973990
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
 

Author Comment

by:redouard
ID: 22986904
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question