Stored procedure body missing

Question

I was going through one of our databases in order to answer a question from the boss, and I peeked at a stored procedure. As usual, I right-clicked it in SSMS, clicked Script stored procedure as... then create, and to my surprise, all I found was the CREATE PROCEDURE statement:

To double-check, I had someone else look and they got the same thing. They were signed in as database owner, I was signed in as sysadmin. I next ran

select
m.definition
from sys.procedures p
left join sys.sql_modules m on p.object_id=m.object_id
where p.name in ('spPUpdateContactUserLockout', 'spPLINK_SYNC_CONTACT');

and got this (note I included another stored proc for comparison purposes)

I've not seen this before. Ever. I didn't think one could create a stored procedure without defining a body, and there are several such procedures in multiple databases on this instance. At this point I'm concerned, but I'm not sure if I don't have to be or if I should be more concerned than I currently am.

This instance is SQL Server 2017 Standard x64 RTM-CU11, and I'm using SSMS 17.9. Any insight is appreciated!

Answer 1

Yes, this is possible, no body required. How it got this way? Your guess is as good as ours. It is easy to do but we can't possibly know whether this was human error or some kind of truncation from a script or tool.

CREATE PROCEDURE dbo.blat
AS
GO
EXEC dbo.blat;

Output:

Command(s) completed successfully.

Good to have source control to sort this kind of thing out.

If you're concerned, though, you should check your application and the rest of the procedures to see if anything calls this empty procedure and actually expects it to do anything. Error handling, unit tests, etc. may be passing simply because it isn't throwing an error.