Sounds like you've already made the decision to use T-SQL for your back-end. For OWASP reasons, you are correct in that calling only stored procedures from your data access layer is the safest approach to avoid SQL injection.
Stored procedures and functions are similar in that you can pass arguments for the parameter list. They also can do similar things. Code re-use is a good thing and will save you in the end when fixing bugs or making enhancements in the future. So find the logic that is similar and re-factor that code to functions. If you want to return tables (single result set), use table based functions. If you want to return single values, use scalar functions. Note, you won't be able to perform permanent environment changes with functions (no CRUD operations are allowed... CREATE/INSERT, READ, UPDATE, DELETE). It's also good practice to wrap your stored procedures with error/exception handling and return those exceptions to the server, to let your logging handle them appropriately. Also note that it's common to return multiple result sets in a single call to a stored procedure (3 SELECT statements for example). In your application code, you'd handle each table individually in the response.
Here are some differences:
While I am in full agreement that source control is the right way to do this, I also understand that not all environments are disciplined enough to rely on that alone (if at all), and that sometimes changes have to made directly to keep the app running, save a client, what have you.
You can use a DDL trigger to keep all revisions in a table in a separate database (and of course back up that database frequently). Assuming you have a utility database:
USE Utility;
GO
CREATE TABLE dbo.ProcedureChanges
(
EventDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
EventType NVARCHAR(100),
EventDDL NVARCHAR(MAX),
DatabaseName NVARCHAR(255),
SchemaName NVARCHAR(255),
ObjectName NVARCHAR(255),
HostName NVARCHAR(255),
IPAddress VARCHAR(32),
ProgramName NVARCHAR(255),
LoginName NVARCHAR(255)
);
Now in your database, first let's grab what we'll call "initial control" - the current version of the stored procedures:
USE YourDB;
GO
INSERT Utility.dbo.ProcedureChanges
(
EventType,
EventDDL,
DatabaseName,
SchemaName,
ObjectName
)
SELECT
N'Initial control',
OBJECT_DEFINITION([object_id]),
DB_NAME(),
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
FROM
sys.procedures;
Now to capture subsequent changes, add a DDL trigger to the database:
USE YourDB;
GO
CREATE TRIGGER CaptureStoredProcedureChanges
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @EventData XML = EVENTDATA(), @ip VARCHAR(32);
SELECT @ip = client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;
INSERT Utility.dbo.ProcedureChanges
(
EventType,
EventDDL,
SchemaName,
ObjectName,
DatabaseName,
HostName,
IPAddress,
ProgramName,
LoginName
)
SELECT
@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
DB_NAME(), HOST_NAME(), @ip, PROGRAM_NAME(), SUSER_SNAME();
END
GO
Over time it will become easy to see and compare changes to procedures, watch new procedures get added to the system, see procedures get dropped, and have a good idea of who to talk to about any of these events.
More information here:
http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/
Best Answer
No, there is no built-in way to do this within SQL Server. As Marian stated, you could recompile all of your procedures to be sure they're still valid, but this doesn't prove they'll still work (and don't forget that deferred name resolution makes this validation less than stellar anyway). However you can use other tools such as SQL Server Data Tools (SSDT) to help facilitate unit testing.
Just like changes to your application, if you change your schema, you need to test those changes. How automated you make your unit testing depends on the complexity of your schema, the consuming application(s), and the number of permutations possible for stored procedure outcomes (including both explicit input of different parameters or different parameter values, and implicit input such as time of day, state of the system, specific data at rest, etc.). You have to keep in mind that in some cases you will expect the stored procedure output to stay the same, but in others you will actually expect it to be different.
Some potentially helpful links (a couple culled from a question on SO geared to testing a single stored procedure):