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.
