Checking MS SQL Server Stored Procedures

When you create stored procedures, MS SQL Server does not really compile them and check them against the system catalog, as for example Oracle does.

This makes it nearly impossible for TSQL developers to make sure their code executes without faults in production environments.

My approach to check stored procedures is to execute them with all parameters set to NULL inside a transaction that will be rolled back after stored procedure execution.

BEGIN TRANSACTION
EXEC (@storedproc)
ROLLBACK TRANSACTION

The simplest strategy is to iterate through all procedures, and execute them using this code inside a TRY … CATCH block. If an error is raised, the CATCH block logs the error.

However, when I ran the code in an actual development environment with lots of SPs (around 120), two problems occurred that needed to be dealt with:

• If any SQL statement run in Management Studio creates more than 100 result sets, you get the error message

The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

• Therefore, we stored the results of analysed procedures in a temporary table, which needs to be created only once if it does not yet exist
• If you execute several stored procedures in a row, and each one probably has some imperfect transaction handling code, you need to make sure to rollback all open transactions.

/*
DROP TABLE #p
DELETE FROM #p
*/

SET ANSI_WARNINGS OFF

IF OBJECT_ID('tempdb..#p') IS NULL
  CREATE TABLE #p (
    ProcedureName sysname COLLATE Latin1_General_CI_AS,
    ErrorNumber int,
    ErrorSeverity int,
    ErrorState int,
    ErrorProcedure sysname COLLATE Latin1_General_CI_AS NULL,
    ErrorLine int,
    ErrorMessage NVARCHAR(2048) COLLATE Latin1_General_CI_AS
  )

DECLARE @proc sysname, @params VARCHAR(1000), @exec VARCHAR(1000)

DECLARE c CURSOR for
SELECT	p.name, SUBSTRING(REPLICATE(', null', COUNT(pa.name)), 3, 1000)
FROM sys.procedures p
LEFT OUTER JOIN sys.parameters pa  ON  p.object_id = pa.object_id
LEFT OUTER JOIN #p ON p.name = #p.ProcedureName
WHERE	p.name LIKE '%stored-procedure-name-filter%'
AND	#p.ProcedureName IS null
GROUP BY p.name
ORDER BY p.name

OPEN c
FETCH c INTO @proc, @params

WHILE @@FETCH_STATUS = 0 BEGIN

  PRINT @proc

  WHILE @@TRANCOUNT > 0
    ROLLBACK

  INSERT INTO #p (ProcedureName) VALUES (@proc)

  BEGIN TRANSACTION

  BEGIN TRY
    SET @exec = 'EXEC ' + @proc + ' ' + ISNULL(@params, '')
    EXEC (@exec)
    ROLLBACK TRANSACTION
  END TRY
  BEGIN CATCH
    WHILE @@TRANCOUNT > 0
      ROLLBACK

    UPDATE #p
    SET ErrorNumber = ERROR_NUMBER(),
      ErrorSeverity = ERROR_SEVERITY(),
      ErrorState = ERROR_STATE(),
      ErrorProcedure = ERROR_PROCEDURE(),
      ErrorLine = ERROR_LINE(),
      ErrorMessage = ERROR_MESSAGE()
    WHERE ProcedureName = @proc
  END CATCH

  FETCH c INTO @proc, @params
END

CLOSE c
DEALLOCATE c

SELECT * FROM #p
WHERE ErrorNumber IS NOT NULL
AND ErrorNumber <> 515

Some final comments:

• I left the DROP and DELETE statements in TSQL comments to make it easier to clear or delete the temp table.
• The collation declarations are necessary for the JOIN condition if your working db and your temp db have different collations.
• If the script exits with the 100-result-sets error, rerun it until it only shows the check result for all stored procedures.
• ErrorNumber 515 is the error indicating a NULL value has been inserted into a not-nullable column. Since the script creates this error condition, we need not list it.
• Temp table name #p is used here for brevity. You should use a name which is never used as temp table in your TSQL code.