At work, I have come across several SQL Server stored procedures that are only used by a single job. In that case, wouldn't it just make more sense to run the code in a job step? Is there some benefit from running statements in stored procedures?
These specific stored procedures do not require input variables, nor are they commonly used calculations; they are mostly just complex select statements. Looking for advice on best practice and performance impact.
There should be no material performance difference.
Code in a stored procedure is stored in the user database, present in backups, owned by the database owner, and can be invoked and debugged from anywhere.
Code in a job step is stored in the MSDB system database and owned by the job owner and can only be run through Agent.