Automating T-SQL Testing

I recently became even more lazy than usual and was looking for a way to test all of my stored procedures (there are over 180 of them in my production database). Since we have a limited hardware and software budget, buying extra software isn’t a part of the equation. So, I took it upon myself to put together a solution. My first task was to figure out how to accomplish this. Since my solution needed to take into account the dynamic nature of the environment, I realized that I needed a way to catalog the stored procedures and any possible combination of parameters. I started by creating a table to hold the names of stored procedures and the parameters:``` CREATE TABLE [test].[Procedures]( [ProceduresId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, [ProcedureName] [nvarchar](128) NOT NULL, [Run] [int] NOT NULL, [ParameterName] [nvarchar](255) NULL, [ParameterType] [nvarchar](255) NULL, [ParameterOrder] [int] NULL, [ParameterValue] [nvarchar](255) NULL );


*   **ProcedureName** – clearly this stores the name of the procedure.
*   **Run** – if a procedure has optional parameters, the Run column can be used to distinguish between different combinations of parameters and group them together.
*   **ParameterName** – the name of the parameter.
*   **ParameterType** – this isn’t used in the dynamic code coming up later, but it does help to sort things out in your brain.
*   **ParameterOrder** – again, like ParameterType, this isn’t used in the dynamic code that I wrote, but it does help to keep things in mind. You could easily modify the T-SQL to write the parameters out in order instead of using named parameters.
*   **ParameterValue** – the value of the parameter being passed in during the test run. You could increase this to VARCHAR(4000) or something like that, depending on your needs.

We have a table, but no procedure to test. Here’s a quick one that I wrote to use the [Northwind database](http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en):```
  CREATE PROCEDURE \[dbo\].\[FindCustomer\] (
    @CompanyName NVARCHAR(40) = NULL,
    @ContactName NVARCHAR(30) = NULL
  )
  AS
  SET NOCOUNT ON;

  IF @CompanyName IS NULL AND @ContactName IS NULL
  BEGIN
    RAISERROR('Either Company Name, Contact Name, or both must be supplied', 16, 0);
    RETURN
  END

  DECLARE @sql AS NVARCHAR(MAX);
  DECLARE @newline AS NVARCHAR(2);

  SET @newline = NCHAR(13) + NCHAR(10);

  SET @CompanyName = '%' + @CompanyName + '%';
  SET @ContactName = '%' + @ContactName + '%';

  SET @sql = '
  SELECT CustomerID, 
         CompanyName, 
         ContactName
    FROM dbo.Customers
   WHERE 1 = 1 ' + @newline;

  IF @CompanyName IS NOT NULL
    SET @sql += '   AND CompanyName LIKE @company\_name ' + @newline;

  IF @ContactName IS NOT NULL
    SET @sql += '   AND ContactName LIKE @contact\_name ' + @newline;

  EXEC sp\_executesql @sql,
       N'@company\_name AS NVARCHAR(40), @contact\_name AS NVARCHAR(30)',
       @CompanyName, @ContactName;

  GO

```Pretty cool, eh? It just selects customers that match either the Company Name, Contact Name, or both. But not neither. If neither parameter has a value, the procedure will throw an error. Now, let’s load that table we created:```
  INSERT INTO test.Procedures VALUES
    ('FindCustomer', 1,	NULL,	NULL,	NULL,	NULL);
  INSERT INTO test.Procedures VALUES
    ('FindCustomer', 2, '@CompanyName',	'NVARCHAR(40)',	1, 'A');
  INSERT INTO test.Procedures VALUES
    ('FindCustomer', 3, '@CompanyName', 'NVARCHAR(40)', 1, 'A');
  INSERT INTO test.Procedures VALUES
    ('FindCustomer', 3, '@ContactName', 'NVARCHAR(30)', 2, 'Maria');

```Fantastic, now there’s data and a procedure to test. How do we go about running these tests? Well, with a little bit of dynamic SQL trickery we can accomplish this pretty easily. **Note:** I’ve updated this to not have syntax highlighting because of a problem with the GeSHi syntax highlighting code that was turning \[text()\] into \[TEXT()\]. This, in turn, was causing a syntax error in the resulting SQL, which I tracked down through the [SQL Server Central forums](http://www.sqlservercentral.com/Forums/Topic726908-149-1.aspx).```
  SET NOCOUNT ON;

  IF OBJECT\_ID(N'tempdb..temp') IS NOT NULL
    DROP TABLE #temp;

  IF OBJECT\_ID(N'tempdb..procs') IS NOT NULL
    DROP TABLE #procs;

  DECLARE @s AS NVARCHAR(1);
  DECLARE @sql AS NVARCHAR(MAX);
  DECLARE @newline AS NVARCHAR(2);
  DECLARE @proc\_sql AS NVARCHAR(MAX);

  SET @s = N'''';
  SET @sql = N'';
  SET @newline = NCHAR(13) + NCHAR(10);

  SET @sql = N'
  DECLARE @error AS BIT;
  DECLARE @errors TABLE (
    ErrorNumber INT,
    ErrorSeverity INT,
    ErrorState INT,
    ErrorProcedure NVARCHAR(126),
    ErrorLine INT,
    ErrorMessage NVARCHAR(2048)
  );
  
  SET @error = 0;
  
  BEGIN TRANSACTION;' + @newline + @newline;
  
  SET @proc\_sql = N'BEGIN TRY
    --myproc--
  END TRY
  BEGIN CATCH
    SET @error = 1;

    INSERT INTO @errors VALUES 
    (
  	  ERROR\_NUMBER(),
      ERROR\_SEVERITY(),
      ERROR\_STATE(),
      ERROR\_PROCEDURE(),
      ERROR\_LINE(),
      ERROR\_MESSAGE()
    );
  END CATCH' + @newline + @newline;

  SELECT ProcedureName,
         Run,
         ParameterOrder,
         ParameterName 
         + N' = '
         + QUOTENAME(ParameterValue, @s) AS x
    INTO #temp
    FROM test.\[Procedures\]
   ORDER BY Run, ParameterOrder;

  SELECT N'EXEC ' 
         + ProcedureName 
         + N' ' 
         + COALESCE(\[params\], '') AS command
    INTO #procs
    FROM (SELECT t.ProcedureName, 
                 t.Run,
                 STUFF((SELECT N', '
                               + x AS \[text()\]
                          FROM #temp AS t2
                         WHERE t2.ProcedureName = t.ProcedureName
                           AND t2.Run = t.Run
                         ORDER BY t2.ParameterOrder
                           FOR XML PATH('')), 1, 1, '') AS \[params\]
            FROM #temp AS t
    ) AS x
  GROUP BY ProcedureName, \[params\], Run
  ORDER BY Run;

  SELECT @sql += REPLACE(@proc\_sql, N'--myproc--', command)
    FROM #procs;

  SET @sql += @newline + @newline
            + 'IF @error = 1
    SELECT \* FROM @errors;
    
    ROLLBACK TRANSACTION;'

  EXEC sp\_executesql @sql;

  DROP TABLE #temp;
  DROP TABLE #procs;

```This procedure creates dynamic SQL that will call all of the stored procedures that we’ve put in the test.Procedures table. You could even integrate this with something like TSQLUnit to create a more robust, dynamic, unit testing framework. With some additional trickery and modifications to the test.Procedures table (or a second table), this could be modified to call every function you’ve created as well.