How do I programatically perform a Modify on all stored procedures in my database in SQL 2008

Question

What I want to do is simulate right clicking a stored procedure an selecting Modify, then execute so that my stored procedure runs.

Some of the tables in our database have changed and not all the sp's have been modified. ie old SP =

ALTER PROCEDURE [dbo].[myProcedure] SELECT name, address, typename from names GO

Then the names table was modified and the typename column removed.

If i click modify on the SP then execute I get an error message in the messages output window.

I would like to do this for every sp in my database so i can see that it runs without errors. (we have 200 sps and it would take a long time to do it manually)

Any ideas would be much appreciated.

Answer 1

You could write a cursor to run through each of them execution them. But how would you know what values to provide for the input parameters? If none of them have parameters something like this will work.

DECLARE @proc sysname
DECLARE cur CURSOR FOR SELECT '[' + schema_name(schema_id) + '].[' + name + ']'
FROM sys.procedures
OPEN cur
FETCH NEXT FROM cur INTO @proc
WHILE @@FETCH_STATUS = 0
BEGIN
     EXEC (@proc)
     FETCH NEXT FROM cur INTO @proc
END
CLOSE cur
DEALLOCATE cur

Handling parameters (assuming you can figure out the values to use) would be along the same lines with an inner loop to get the parameter names, then supply them with values.

Answer 2

You should compose a text file of test cases in the form:

exec <stored proc> [args]
if (@@error <> 0)
begin
  print "Fail"
end    
go

Unfortunately there is no way to automate this further unless either:

• None of your stored procedures take parameters.
• Your stored procedure parameters are derivable (highly unlikely).

Even if you do supply one particular set of parameter values, this isn't comprehensively testing that all stored procs in your database are bug free. It just verifies that the sproc runs for those particular arguments. The bottom line: There are no short-cuts when it comes to proper unit testing.