Customizing the PRINT statement

Question

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.

Does @@ROWCOUNT already know, or do I need to define it? I apologize, I used to be closer to an Intermediate user but I haven't touched it in about a year. — Johnny Bones
– Johnny Bones, Commented Jun 1, 2022 at 15:07
When wanting to include a single quote in a string literal you have to escape it with another single quote. So you string would be something like 'it doesn''t already' — Sean Lange
– Sean Lange, Commented Jun 1, 2022 at 15:08

Thom A · Accepted Answer · 2022-06-01 15:20:21Z

3

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).');

edited Jun 1, 2022 at 15:20

answered Jun 1, 2022 at 15:08

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Charlieface Over a year ago

"after the statement completed" in this case being immediately after, otherwise @@ROWCOUNT will not be correct

Johnny Bones Over a year ago

Had to actually set NOCOUNT to ON, but that was just a quick google to fix it.

Thom A Over a year ago

Yep, sorry, brain was in reverse there, @JohnnyBones , it's already in holiday mode as it's a 4 day weekend here. Fixed.

Collectives™ on Stack Overflow

Customizing the PRINT statement

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related