Customizing the PRINT statement-CodePudding

I've got 2 PRINT statements in my code that look like this:

DECLARE @Msg VARCHAR(300)= 'Delete Email addresses that have been in the table for more than 30 days.';
PRINT @Msg;

-- If the email address has been in the table for more than 30 days, it should be removed
DELETE
--SELECT *
FROM CLNT_SVC_GOLD_SURVEY_EMAILS
WHERE DATE_ADDED < DATEADD(day, -30, GETDATE())


DECLARE @Msg1 VARCHAR(300)= 'Append data to the final table if it doesnt already exist.';
PRINT @Msg1;

-- Copy all records from the staging table where no existing email address exists in the final table
INSERT INTO CLNT_SVC_GOLD_SURVEY_EMAILS(ACCOUNT_FULL_NAME, ACCOUNT_NUMBER, CUSTOMER_FULL_NAME, CONTACT_EMAIL, DATE_ADDED)
SELECT 
A.ACCOUNT_FULL_NAME,
A.ACCOUNT_NUMBER,
A.CUSTOMER_FULL_NAME,
A.CONTACT_EMAIL,
CONVERT(DATE, GETDATE()) as DATE_ADDED
FROM CLNT_SVC_GOLD_SURVEY A
LEFT JOIN CLNT_SVC_GOLD_SURVEY_EMAILS B
  ON A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER
  AND A.CONTACT_EMAIL = B.CONTACT_EMAIL
WHERE B.DATE_ADDED IS NULL
  AND A.ACCOUNT_FULL_NAME NOT LIKE '%UNKNOWN%'

The output looks like this:

Delete Email addresses that have been in the table for more than 30 days.

(0 row(s) affected)
Append data to the final table if it doesnt already exist.

(0 row(s) affected)

I've got a couple questions. First, is there any way to integrate the number of records into the custom message and remove them from the automated message? i.e.:

Delete Email addresses that have been in the table for more than 30 days. This step affected 0 row(s).

Append data to the final table if it doesnt already exist. This step affected 0 row(s).

Second, how do you get a custom print message to print an apostrophe? I had to remove it from the word "doesnt" because I couldn't get it to work.

CodePudding user response：

You need to turn NOCOUNT to ON, meaning you don't get the (0 row(s) affected) messages and then PRINT after the statement completed and incorporate the value of @@ROWCOUNT into printed message:

SET NOCOUNT ON;

DELETE
FROM dbo.CLNT_SVC_GOLD_SURVEY_EMAILS
WHERE DATE_ADDED < DATEADD(day, -30, GETDATE());

PRINT CONCAT(N'Delete Email addresses that have been in the table for more than 30 days. This step affected ', @@ROWCOUNT,N' row(s).');