Testing stored procedures which use dynamic SQL

Consider the following stored procedure which uses dynamic SQL:

CREATE PROCEDURE Readers.SelectEmployeesByName

@FirstName VARCHAR(20),

@LastName VARCHAR(20),

@Status VARCHAR(6)

AS

IF @Status IS NOT NULL AND @Status NOT IN('Active', 'Quit') BEGIN

RAISERROR('Readers.SelectEmployeesByName: Invalid Status %s', 16, 1, @Status)

RETURN -1

END

IF @FirstName IS NULL AND @LastName IS NULL AND @Status IS NULL BEGIN

RAISERROR('Readers.SelectEmployeesByName: MustProvide At Least One NOT NULL Parameter', 16, 1)

RETURN -1

END

DECLARE @SQL NVARCHAR(3000), @SQLSeparator VARCHAR(5), @params NVARCHAR(1000), @comma VARCHAR(1)

SELECT @SQLSeparator = 'WHERE',

@params = '@FirstName VARCHAR(20), @LastName VARCHAR(20), @Status VARCHAR(6)',

-- @comma = ''

@SQL = 'SELECT FirstName, LastName, Status, HireDate FROM data.Employees '

IF @FirstName IS NOT NULL BEGIN

SET @SQL = @SQL + @SQLSeparator + ' FirstName = @FirstName '

--SET @params = @params + @comma + '@FirstName VARCHAR(20)' + @comma

SET @SQLSeparator = 'AND'

--SET @comma = ','

END

IF @LastName IS NOT NULL BEGIN

SET @SQL = @SQL + @SQLSeparator + ' LastName = @LastName '

--SET @params = @params + @comma + '@LastName VARCHAR(20)' + @comma

SET @SQLSeparator = 'AND'

--SET @comma = ','

END

IF @Status IS NOT NULL BEGIN

SET @SQL = @SQL + @SQLSeparator + ' Status = @Status'

END

-- PRINT for debugging purposes only

PRINT @SQL

EXEC sp_executeSQL @SQL, @params, @FirstName, @LastName, @Status

GO

It is very easy to make sure that the procedure always generates valid SQL which compiles:

DECLARE params CURSOR LOCAL STATIC

FOR SELECT FirstName, LastName, Status

FROM (SELECT 'Jeff' FirstName UNION ALL SELECT NULL) t1 CROSS JOIN

(SELECT 'Jones' LastName UNION ALL SELECT NULL) t2 CROSS JOIN

(SELECT 'Active' Status UNION ALL SELECT 'Retired' UNION ALL SELECT NULL) t3

DECLARE @FirstName VARCHAR(20),

@LastName VARCHAR(20),

@Status VARCHAR(6),

@keepFetching INT

SET @keepFetching = 1

OPEN params

WHILE @keepFetching = 1 BEGIN

FETCH params INTO @FirstName, @LastName, @Status

IF @@FETCH_STATUS <> 0 BEGIN

SET @keepFetching = 0

END ELSE BEGIN

EXEC Readers.SelectEmployeesByName @FirstName, @LastName, @Status

END

END

CLOSE params

DEALLOCATE params

Even though the procedure takes only three parameters, there are twelve different combinations that need to be considered. Five combinations are invalid and are rejected without running dynamic SQL. Seven combinations out of twelve are valid and as such need to be tested. Running seven unit tests is quite reasonable. Suppose, however, that more parameters are added to the procedure:

CREATE PROCEDURE Readers.SelectEmployees

@FirstName VARCHAR(20),

@LastName VARCHAR(20),

@Status VARCHAR(6),

@HireDateFrom DATETIME,

@HireDateTo DATETIME

Now you need to test at least forty eight combinations. It is still easy to make sure that dynamic SQL always compiles - you just need to add two more cross joins to your cursor. However, adding more and more unit tests to test results for all possible combinations becomes less and less feasible. Usually you want to test only the combinations that you actually need, and you do not want to expose untested combinations. For example, you could create the following two procedures:

CREATE PROCEDURE Readers.SelectEmployeesByNameAndStatus

@FirstName VARCHAR(20),

@LastName VARCHAR(20),

@Status VARCHAR(6)

AS

-- twelve combinations exposed by this procedure

DECLARE @ret INT

EXEC @ret = Readers.SelectEmployees @FirstName, @LastName, @Status, NULL, NULL

RETURN @ret

GO

CREATE PROCEDURE Readers.SelectEmployeesByHireDate

@HireDateFrom DATETIME,

@HireDateTo DATETIME

AS

-- four combinations exposed by this procedure

DECLARE @ret INT

EXEC @ret = Readers.SelectEmployees NULL, NULL, NULL, @HireDateFrom, @HireDateTo

RETURN @ret

GO

That done, you could revoke permissions to execute the original procedure, Readers.SelectEmployees - that would reduce the number of combinations which you need to test fully to only sixteen. Clearly there are other ways to deal with this problem, but in any case avoid exposing untested code.

---