自定义PRINT语句

Question

我的代码中有两个PRINT语句，看起来像这样:

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%'

输出如下所示:

删除在表中存在超过30年的Email地址天。

(0 row(s)受影响)
将不存在的数据追加到最终表

(受影响的0行)

我有几个问题。首先，是否有办法将记录的数量集成到自定义消息中，并从自动消息中删除它们?例如:

删除在表中存在超过30天的Email地址。此步骤影响0行。

将不存在的数据追加到最终表。此步骤影响0行。

第二，如何让自定义打印消息打印撇号?我不得不把它从单词"doesn’t"中去掉。因为我不能让它工作。

Answer 1

您需要将NOCOUNT变为ON，这意味着您不会在语句完成后获得(0 row(s) affected)消息，然后PRINT，并将@@ROWCOUNT的值合并到打印消息中:

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