[9.5.452 Pro] - B: $$...$$ macro result in SQL Server

[9.5.452 Pro] - B: $$...$$ macro result in SQL Server

I've got a snippet that I use to quickly retrieve codes for eg. stored procedures, functions or triggers. It's very old and it's pretty crude but I'm still using it every now and then. Below is its code:

Code:

$$
SET ARITHABORT ON
DECLARE @obj_qualname sysname = '$OBJECT(ins_qualname)$';

IF (
@@version LIKE 'Microsoft SQL Server 2005%'
OR @@version LIKE 'Microsoft SQL Server 2008%'
or @@version like '%SQL Server 201%'
or @@version like '%Denali%')
BEGIN
DECLARE
@sql NVARCHAR(MAX)

SELECT
@sql = ''

SET @sql = (
SELECT
STUFF(
aha.[definition]
,CHARINDEX('CREATE' ,aha.[definition])
,6
,'ALTER'
)
+ CASE WHEN Substring(aha.definition,LEN(aha.definition),1) NOT IN (CHAR(10), CHAR(13)) THEN CHAR(10) ELSE '' END
+ 'GO' + CHAR(10)
+ CASE
WHEN aha.disable_trigger <> '' THEN CHAR(10) + aha.disable_trigger ELSE ''
END + CHAR(10) + CHAR(10)
FROM
(
SELECT
sm.[definition]
,'' AS disable_trigger
FROM
sys.all_sql_modules AS sm
WHERE sm.[object_id] = OBJECT_ID(@obj_qualname)

UNION

SELECT
sm.[definition]
,CASE
WHEN tr.is_disabled = 1 THEN 'DISABLE TRIGGER ' + tr.name + ' ON ' + SCHEMA_NAME(t.[schema_id]) + '.' + t.name + CHAR(10) + 'GO'
+ CHAR(10)
+
CHAR(10) ELSE ''
END AS disable_trigger
FROM
sys.tables AS t
INNER JOIN
sys.triggers AS tr
ON tr.parent_id = t.[object_id]
INNER JOIN
sys.all_sql_modules AS sm
ON sm.[object_id] = tr.[object_id]
WHERE t.[object_id] = OBJECT_ID(@obj_qualname)
) AS aha
FOR XML PATH(''), TYPE
).value('(./text()[1])' ,'nvarchar(max)')

SELECT
@sql
END
ELSE
SELECT
STUFF(sc.[text], CHARINDEX('CREATE', sc.[text], 1), 6, 'ALTER')
FROM
[$DB$].dbo.syscomments AS sc
WHERE sc.id = OBJECT_ID(@obj_qualname)
ORDER BY
sc.colid;

IF OBJECT_ID(@obj_qualname, N'P') IS NOT NULL
SELECT
CASE
WHEN perm.state <> 'W' THEN perm.state_desc
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.[schema_id])) + '.' + QUOTENAME(obj.name) COLLATE
database_default
+ CASE
WHEN cl.column_id IS NULL THEN SPACE(0)
ELSE '(' + QUOTENAME(cl.name) + ')'
END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name) COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
+ ';'
+ CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) AS '--Object Level Permissions'
FROM
sys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id
AND cl.[object_id] = perm.major_id
WHERE 1 = 1
AND obj.[object_id] = OBJECT_ID(@obj_qualname);

IF OBJECT_ID(@obj_qualname ,N'P') IS NOT NULL
SELECT
-- CONCAT
('ADD SIGNATURE TO OBJECT::[$OBJECT(ins_schema)$].[$OBJECT(ins_object)$] BY CERTIFICATE [' + c.[name] + '] WITH PASSWORD = '''';' + char(13) + char(10) +'GO')
FROM
sys.procedures AS pr
INNER JOIN
sys.sql_modules AS sm
ON sm.[object_id] = pr.[object_id]
INNER JOIN
sys.crypt_properties AS cp
ON cp.major_id = pr.[object_id]
INNER JOIN
sys.certificates AS c
ON c.thumbprint = cp.thumbprint
WHERE 1 = 1
AND pr.[object_id] = OBJECT_ID(@obj_qualname)
;
--$$

Now, I've run into a very interesting issue. For some stored procedures the snippet retrieves their code multiple times. If I remove the $$ parts and only let SA create the code and then execute it manually, the result contains the edit script for the stored procedure as it is expected. However, if I allow SA to retrieve the script using the $$...$$ macro and put it into the editor, the result contains the script concatenated 3 times. I've got yet no idea what triggers this behavior on some procedures and not on others.

Maybe a coincidence, but inside $$..$$ you got 3 occurrences of [$OBJECT(ins_object)$], and you say the code is executed 3 times.

Is there anything special about the procedures that trigger it 3 times? case sensitive names? same schema, vs. different schema or database?

I believe it is a coincidence. If that would matter, it would consistently yield the single or the triple results for each stored procedure. Also, those aren't all $OBJECT(ins_object)$ but yes, there are 3 $OBJECTS(...)$ macros: $OBJECT(ins_qualname)$, $OBJECT(ins_schema)$ and $OBJECT(ins_object)$. The locations of those are why I said it's crude and old. I usually collect macros to the top part of my snippets and keep the main code clean of any macros if possible. This one I haven't touched in ages.

I'm not sure it triggers 3 times, but it definitely returns the results of an execution 3 times. I see no relevant common properties in affected procedures or dissimilarities to those unaffected. I'll try to clean up the snippet code to see if it makes any difference.

EDIT: Okay, the clean up did not help. The new code contains one $OBJECT(ins_qualname)$ and the $$...$$ encapsulating the entire script. It still returns the results 3 times for the same procedure.

The new code is:

Code:

$$
SET ARITHABORT ON
DECLARE @obj_qualname sysname = '$OBJECT(ins_qualname)$';;
DECLARE @ins_schema sysname = PARSENAME(@obj_qualname, 2)
,@ins_object sysname = PARSENAME(@obj_qualname, 1)
;

IF (
@@version LIKE 'Microsoft SQL Server 2005%'
OR @@version LIKE 'Microsoft SQL Server 2008%'
or @@version like '%SQL Server 201%'
or @@version like '%Denali%')
BEGIN
DECLARE
@sql NVARCHAR(MAX)

SELECT
@sql = ''

SET @sql = (
SELECT
STUFF(
aha.[definition]
,CHARINDEX('CREATE' ,aha.[definition])
,6
,'ALTER'
)
+ CASE WHEN Substring(aha.definition,LEN(aha.definition),1) NOT IN (CHAR(10), CHAR(13)) THEN CHAR(10) ELSE '' END
+ 'GO' + CHAR(10)
+ CASE
WHEN aha.disable_trigger <> '' THEN CHAR(10) + aha.disable_trigger ELSE ''
END + CHAR(10) + CHAR(10)
FROM
(
SELECT
sm.[definition]
,'' AS disable_trigger
FROM
sys.all_sql_modules AS sm
WHERE sm.[object_id] = OBJECT_ID(@obj_qualname)

UNION

SELECT
sm.[definition]
,CASE
WHEN tr.is_disabled = 1 THEN 'DISABLE TRIGGER ' + tr.name + ' ON ' + SCHEMA_NAME(t.[schema_id]) + '.' + t.name + CHAR(10) + 'GO'
+ CHAR(10)
+
CHAR(10) ELSE ''
END AS disable_trigger
FROM
sys.tables AS t
INNER JOIN
sys.triggers AS tr
ON tr.parent_id = t.[object_id]
INNER JOIN
sys.all_sql_modules AS sm
ON sm.[object_id] = tr.[object_id]
WHERE t.[object_id] = OBJECT_ID(@obj_qualname)
) AS aha
FOR XML PATH(''), TYPE
).value('(./text()[1])' ,'nvarchar(max)')

SELECT
@sql
END
ELSE
SELECT
STUFF(sc.[text], CHARINDEX('CREATE', sc.[text], 1), 6, 'ALTER')
FROM
dbo.syscomments AS sc
WHERE sc.id = OBJECT_ID(@obj_qualname)
ORDER BY
sc.colid;

IF OBJECT_ID(@obj_qualname, N'P') IS NOT NULL
SELECT
CASE
WHEN perm.state <> 'W' THEN perm.state_desc
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.[schema_id])) + '.' + QUOTENAME(obj.name) COLLATE
database_default
+ CASE
WHEN cl.column_id IS NULL THEN SPACE(0)
ELSE '(' + QUOTENAME(cl.name) + ')'
END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name) COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
+ ';'
+ CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) AS '--Object Level Permissions'
FROM
sys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id
AND cl.[object_id] = perm.major_id
WHERE 1 = 1
AND obj.[object_id] = OBJECT_ID(@obj_qualname);

IF OBJECT_ID(@obj_qualname ,N'P') IS NOT NULL
SELECT
-- CONCAT
('ADD SIGNATURE TO OBJECT::[' + @ins_schema + '].[' + @ins_object + '] BY CERTIFICATE [' + c.[name] + '] WITH PASSWORD = ''Password12we43'';' + char(13) + char(10) +'GO')
FROM
sys.procedures AS pr
INNER JOIN
sys.sql_modules AS sm
ON sm.[object_id] = pr.[object_id]
INNER JOIN
sys.crypt_properties AS cp
ON cp.major_id = pr.[object_id]
INNER JOIN
sys.certificates AS c
ON c.thumbprint = cp.thumbprint
WHERE 1 = 1
AND pr.[object_id] = OBJECT_ID(@obj_qualname)
;
--$$

This has been reproduced and confirmed as a bug. The processing of multiple result sets which are internal to the macro is incorrect. Good news is that will have a fix for that issue availability very soon.

That's great! Thank you very much.

Can you share some info on what made this trigger for some procedures but not on others?