CODEWITHSUNDEEP
.netsql-serverunit-testingstored-proceduresmocking
jparram
jparram

Reputation: 804

Creating mock stored procedures

Is there a simple way to automate/generate mocks or stubs based on sql stored procs?

The situation I am trying to cover is when changes are made to a proc in development, i.e. someone adds a required input parameter, I would then want the test that has not been updated with the required parameter to fail.

Upvotes: 2

Views: 2528

Answers (3)

Dan Snell
Dan Snell

Reputation: 2245

Though less automated another approach would be to create a script that contains a set of execution statements for the stored procedures you are interested in. By running these you could captured the ones that fail when there is a changed parameter. You also could script out the exec store procedure bit in MS SQL

example

exec [DATABASE].dbo.[StoredProc] parameter1, parameter 2

This would not provide any mechanism for verifying results. For a more comprehensive approach you might look at this example put together in the Simple-Talk newsletter that builds a test harness using c# and NUnit.

"Close those Loopholes - Testing Stored Procedures"

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89661

You can generate tests which call the stored procedures (perhaps with dummy variables) by inspecting INFORMATION_SCHEMA.PARAMETERS

If you run sp_recompile on such a test stored proc, it should fail to compile because of the incorrect number of parameters.

Upvotes: 0

Ali
Ali

Reputation: 2233

One solution would be to write tests to verify the number of parameters your stored procedures accept. This would be more of an integration type test than a pure unit test, but it would solve your issur.

Upvotes: 0

Related Questions