Is "SET NOCOUNT ON" a good choice for a placeholder stored procedure body?
To better meet the "criteria" from my question, I've landed on replacing SET NOCOUNT ON
with a RAISERROR
statement.
My code to ensure a stored procedure exists before running an ALTER PROCEDURE
on it ends up looking like:
-- Create the sproc with a temporary body if it doesn't exist yet.
-- We'll set the real body in the ALTER PROCEDURE statement below.
IF NOT EXISTS (
SELECT * FROM sys.objects
WHERE name = 'MyStoredProcedureNameHere'
AND type = 'P'
) BEGIN
EXEC ('CREATE PROCEDURE MyStoredProcedureNameHere AS
RAISERROR (''The ALTER PROCEDURE to set the body for MyStoredProcedureNameHere did not run as it should have!'', 16, 1);');
END
GO
ALTER PROCEDURE MyStoredProcedureNameHere AS ...
So, if the ALTER PROCEDURE somehow fails to run, then if my stored procedure gets executed, it'll raise an error instead of silently doing nothing (as would be the case with a body consisting only of SET NOCOUNT ON
).
Credit for this approach: http://www.codeofhonor.com/blog/a-better-way-to-update-sql-stored-procedures
This is probably because including SET NOCOUNT ON;
is considered good practice. It is included in the template SP, generated by SSMS. From the MSDN article on NOCOUNT:
NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.
The idea is NOCOUNT
is first used a placeholder. Later you add to, rather than replace, this statement.