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
  );

A quick explanation of the columns in the table:

  • 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:

  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.

  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.

Comments

6 Comments so far. Comments are closed.
  1. Yury,

    Hi Jeremiah,

    I came across this method of testing the sproc and I really like it. I am a QA Engineer and we are thinking of adopting that script for our testing purposes.

    I am not an expert in SQL but I could easily follow the steps. So, so far, I created a table and populated it with some seed data for the sproc I am trying to test.

    Then I modified the last chank of code as follows:
    1. Replaced test.[Procedures] with proper DB (actually just left Procedures)
    2. Replaced –myproc– with exec MyDB.dbo.MyProc

    When I ran it, I got whole bunch of errors:
    #1. Complain about invalid XML identifier in Text() as required by FOR XML; ‘(‘ is the first character at fault
    #2. When I removed () around this column name, I got: “Incorrect syntax near the keyword ‘EXEC’. Msg 156, Level 15, State 1. Line 34 AND also Incorrect syntax near ‘>’, same line.

    Any suggestions to make this script run? Appreciate it.
    -Yury

    • Yury, I fixed the issue. It basically boils down to using [TEXT()] instead of [text()] in the query. The T-SQL syntax highlighter that I am using was capitalizing the TEXT because it is also a data type. Let me know if you keep having problems with this code and I’ll help out however I can. Sorry about any confusing that mistake may have caused.

  2. Yury,

    Jeramiah,

    Thank you very much for looking into this and for fixing that! I really appreciate it, there was no confusing: I am glad I found this code!

    The code runs fine now. However, I am now seeing something else results-wise…

    The sproc takes in login name and spits out a true/false depending whether the login is valid.

    What I am getting now is the error table with an entry for each run with the same error message:
    “Procedure or function ‘prc_CheckLogin’ expects parameter ‘@LoginName’, which was not supplied”

    More specifics…

    The #temp table shows:
    ProcName Run ParamOrder x
    prc_CheckLogin 1 1 @LoginName = ”
    prc_CheckLogin 2 1 @LoginName = ‘nonexistent’

    The #procs table shows:
    Command
    EXEC prc_CheckLogin @LoginName = ”
    EXEC prc_CheckLogin @LoginName = ‘nonexistent’

    Somehow, it doesn’t execute the command correctly. In the code, I substituted –myprc– with OtherDB.dbo.prc_checklogin since that sp sits in the different db but on the same server, and it doesn’t look like it can’t access the proc since commands are executed just fine…

    Any suggestions?

    As always, appreciate your time,

    -Yury

  3. Yury,

    OK. Sending it to your GMail address…

    Thanks a LOT!

  4. Hi

    This is great article for sql server developer

    Regards
    Jayant Das
    9313406257

This site is protected with Urban Giraffe's plugin 'HTML Purified' and Edward Z. Yang's Powered by HTML Purifier. 531 items have been purified.