How to detect interface break between stored procedure

I am working on a large project with a lot of stored procedures. I c开发者_JAVA技巧ame into the following situation where a developer modified the arguments of a stored procedure which was called by another stored procedure. Unfortunately, nothing prevents the ALTER PROC to complete.

Is there a way to perform those checks afterwards ? What would be the guidelines to avoid getting into that kind of problems ?

Here is a sample code to reproduce this behavior :

CREATE PROC Test1 @arg1 int
AS
BEGIN
PRINT CONVERT(varchar(32), @arg1)
END
GO

CREATE PROC Test2 @arg1 int
AS
BEGIN
DECLARE @arg int;
SET @arg = @arg1+1;
EXEC Test1 @arg;
END
GO

EXEC Test2 1;
GO

ALTER PROC Test1 @arg1 int, @arg2 int AS
BEGIN
PRINT CONVERT(varchar(32), @arg1)
PRINT CONVERT(varchar(32), @arg2)
END
GO

EXEC Test2 1;
GO

DROP PROC Test2
DROP PROC Test1
GO

---

Sql server 2005 has a system view sys.sql_dependencies that tracks dependencies. Unfortunately, it's not all that reliable (For more info, see this answer). Oracle, however, is much better in that regard. So you could switch. There's also a 3rd party vendor, Redgate, who has Sql Dependency Tracker. Never tested it myself but there is a trial version available.

I have the same problem so I implemented my poor man's solution by creating a stored procedure that can search for strings in all the stored procedures and views in the current database. By searching on the name of the changed stored procedure I can (hopefully) find EXEC calls.

I used this on sql server 2000 and 2008 so it probably also works on 2005. (Note : @word1, @word2, etc must all be present but that can easily be changed in the last SELECT if you have different needs.)

CREATE PROCEDURE [dbo].[findWordsInStoredProceduresViews]
@word1 nvarchar(4000) = null,
@word2 nvarchar(4000) = null,
@word3 nvarchar(4000) = null,
@word4 nvarchar(4000) = null,
@word5 nvarchar(4000) = null
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- create temp table 
    create table #temp
    ( 
        id int identity(1,1),
        Proc_id INT, 
        Proc_Name SYSNAME, 
        Definition NTEXT 
    ) 

    -- get the names of the procedures that meet our criteria 
    INSERT #temp(Proc_id, Proc_Name) 
        SELECT id, OBJECT_NAME(id) 
            FROM syscomments 
            WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1 or
                  OBJECTPROPERTY(id, 'IsView') = 1 
            GROUP BY id, OBJECT_NAME(id) 

    -- initialize the NTEXT column so there is a pointer 
    UPDATE #temp SET Definition = '' 

    -- declare local variables 
    DECLARE  
        @txtPval binary(16),  
        @txtPidx INT, 
        @curText NVARCHAR(4000), 
        @counterId int,
        @maxCounterId int,
        @counterIdInner int,
        @maxCounterIdInner int

    -- set up a double while loop to get the data from syscomments

    select @maxCounterId = max(id)
    from #temp t

    create table #tempInner  
    (
        id int identity(1,1),
        curName SYSNAME, 
        curtext ntext
    ) 


    set @counterId = 0

    WHILE (@counterId < @maxCounterId) 
    BEGIN 
        set @counterId = @counterId + 1

        insert into #tempInner(curName, curtext)
        SELECT OBJECT_NAME(s.id), text 
        FROM syscomments s 
        INNER JOIN #temp t 
        ON s.id = t.Proc_id 
        WHERE t.id = @counterid
        ORDER BY s.id, colid

        select @maxCounterIdInner = max(id)
        from #tempInner t

        set @counterIdInner = 0
        while (@counterIdInner < @maxCounterIdInner)
        begin
            set @counterIdInner = @counterIdInner + 1

            -- get the pointer for the current procedure name / colid 
            SELECT @txtPval = TEXTPTR(Definition) 
                FROM #temp
                WHERE id = @counterId

            -- find out where to append the #temp table's value 
            SELECT @txtPidx = DATALENGTH(Definition)/2 
                FROM #temp 
                WHERE id = @counterId

            select @curText = curtext
            from #tempInner
            where id = @counterIdInner

            -- apply the append of the current 8KB chunk 
            UPDATETEXT #temp.definition @txtPval @txtPidx 0 @curtext 
        end

        truncate table #tempInner
    END 

    -- check our filter 
    SELECT Proc_Name, Definition 
        FROM #temp t
        WHERE (@word1 is null or definition LIKE '%' + @word1 + '%') AND
              (@word2 is null or definition LIKE '%' + @word2 + '%') AND
              (@word3 is null or definition LIKE '%' + @word3 + '%') AND
              (@word4 is null or definition LIKE '%' + @word4 + '%') AND
              (@word5 is null or definition LIKE '%' + @word5 + '%') 
        ORDER BY Proc_Name

    -- clean up 
    DROP TABLE #temp 
    DROP TABLE #tempInner
END

---

You can use sp_refreshsqlmodule to attempt to re-validate SPs (this also updates dependencies), but it won't validate this particular scenario with parameters at the caller level (it will validate things like invalid columns in tables and views).

http://www.mssqltips.com/tip.asp?tip=1294 has a number of techniques, including sp_depends

Dependency information is stored in the SQL Server metadata, including parameter columns/types for each SP and function, but it isn't obvious how to validate all the calls, but it is possible to locate them and inspect them.