Product articles SQL Data Compare Database Builds and Deployments
Testing SQL Server Stored Procedures…

Testing SQL Server Stored Procedures and Functions with SQL Data Compare

Phil Factor demonstrates a cunning way to test stored procedures or functions, such as after refactoring, by storing the 'before' and 'after' results in views and then using SQL Data Compare to spot any discrepancies.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

It is always a painful chore to have to devise a way to test out a stored procedure that returns a result. When Sybase invented the stored procedure, they didn’t really pay too much attention to the way it would need to participate in a SQL query: it was intended either to produce a final report to the screen or be used from an application. They never got around to developing a way of capturing a second, or subsequent, result even though a stored procedure could generate as many as was wanted. The problem with testing out a procedure in SQL is that, traditionally, you had to use the rather clunky INSERT…EXEC to get the result. Then you would have to compare the resulting table with another that had what you knew to be the correct result.

A SQL-based test for a stored procedure is still the quickest way of alerting you to the fact that there is a problem. However, it is likely to do little more than tell you whether a procedure passed or failed. The whole point of using SQL Data Compare to do this sort of thing, is that you can find out very quickly what and where the difference was. There is nothing that is more likely to lead to a quick debug than knowing precisely what is wrong.

The problem is that SQL Data Compare doesn’t ‘do’ stored procedures. It just does tables and, if you opt for them, views. SQL Compare has this restriction because other SQL objects that produce results don’t necessarily have an obvious key field that one can match to compare the content of the rows. Without a key, you don’t know what rows to compare to see if they are the same. They are also likely to produce different results when given different parameters.

Why not make a stored procedure into a view for test purposes? Well, OK, but you still have the difficulty that you must supply one or more parameters and can only test with that set of parameters. You also have the problem that you probably don’t want those views to be permanent in your databases. You would have to build up and tear down.

Here’s a way of doing it that requires no alteration to either the reference version of the database, or the build that you are testing. Instead, you create a test database for the purpose of checking out the stored procedures that need to be consistent in their results between releases with the same set of data and parameters.

Getting the results from remote routines into local views

Let’s imagine that we have two databases, one of which is the Development version of the database and one if which is our reference version (representing the current Staging or Production version). On our dev server, we’ve changed the table design, affecting several reporting stored procedures, which consequently have had to be altered as well (see my previous article, The Database Development Stage for the details). Nonetheless, when executed with the same data and parameters, they are required to return the same result.

To test these, we create two ‘routine views’ databases on our development database server. The term ‘routine views’ means that they exist only to turn routines such as table-valued functions and procedures into views, so they can be tested with SQL Data Compare. These databases are used just for this purpose, and aren’t stored as part of the database build, so it is a clean way of running a series of tests.

We can create a view from a SQL query that calls a local or remote stored procedure using either OPENQUERY or OPENROWSET.

If you have a linked server set up you can do this….

It has the advantage that it doesn’t require credentials in the view definition, if you are using SQL Server authentication (more on this at the end of the article). Otherwise, or if you want the option to link back to the same server you can use the OPENROWSET version. We’ll choose this version, just to be able to demonstrate it all on the one server.

I show some connection string examples later. This will allow access locally or across a network so that we can keep these ‘routine views’ databases local and no code needs to be installed on the actual databases.

Before you run the above code, you will probably need to flick the switch to allow this type of query.

Although this technique works, it does not extend to a stored procedure that produces multiple result sets. For these, if you have them, your tests will need to be in PowerShell.

You will have noticed that we have added a RowNumber column to impose some sort of default key column. You are going to have to specify a suitable key field in your SQL Data Compare project, and this provides a lifebelt. This RowNumber column can only be useful if the order of rows is going to be the same but it is doing no harm in being there even if it isn’t used or needed.

I’ve just shown a view being used with a procedure. It is even easier with a function.

That reference to the original server in the OPENROWSET invocation of the function is sometimes necessary, I’ve found. Though this example works, it isn’t the sort of test I’d want to run. I’d want a bigger table to allow SQL Data Compare to take a bit of exercise and test out a lot more parameter values.

We will need to create at least one view for every stored procedure that we want to test but we’ll probably want to create more than one copy of each view, so that we can test different parameter values.

Although we only need to do this just once, it would be a bit of a chore. To kick-start the process, you could use the following code to generate a build script to create a view for every stored procedure in MyDatabase:

To get this to run on older versions of SQL Server, you’ll need to substitute the XML concatenation trick for the string_agg function. A similar script will do your table-valued functions if you want to do them as well.

Using our reference version of the Pubs database as MyDatabase, this would, if you switch to text view in SSMS, give the following build script for all the stored procedures in the current release of Pubs (you may need to go to Query > query options > text in SSMS and adjust the ‘maximum number of chars displayed in each column’).

CREATE OR ALTER VIEW byroyaltypubs AS 
FROM OPENROWSET('SQLNCLI', 'Server=MYNICESERVER;Database=Pubs;Trusted_Connection=yes;',
    'EXEC dbo.byroyalty @percentage') AS TheResult
FROM OPENROWSET('SQLNCLI', 'Server=MYNICESERVER;Database=Pubs;Trusted_Connection=yes;',
    'EXEC dbo.reptq1 ') AS TheResult
FROM OPENROWSET('SQLNCLI', 'Server=MYNICESERVER;Database=Pubs;Trusted_Connection=yes;',
    'EXEC dbo.reptq2 ') AS TheResult
FROM OPENROWSET('SQLNCLI', 'Server=MYNICESERVER;Database=Pubs;Trusted_Connection=yes;',
    'EXEC dbo.reptq3 @hilimit, @lolimit, @type') AS TheResult

When using this shortcut, I then delete any procedures I don’t want to test, and for the rest I substitute test values for the generated parameter names, which are only there to prompt you to provide good values. If you need to test for different values, then do it at this stage by copying and pasting the CREATE OR ALTER VIEW statement, renaming the view, and changing the relevant value.

If this auto-generation trick seems a bit ambitious, then it is perfectly fine to run hand-cut code to deal with just the tests you want to focus on. Once you’re done, run the whole build script on your empty “Reference routine views” database.

I then save this, and copy the entire build script into another query pane, switch the USE statement to the ‘Dev routine views‘ database, and change the connection strings in the OPENROWSET function to the current development build of pubs (I just use SSMS’s ctrl-F, “Replace in Files”). If the names of any routines have changed because of the refactoring, I’ll also need to update them in the build script, to match, and deal with any other changes between the versions that we want to test.

When both scripts are executed, we should have two temporary databases with nothing in them but views that contain procedures and functions. We’re now all set.

Comparing the results using SQL Data Compare

Even if you don’t have SQL Data Compare, you will probably notice that you can run a test in a single script on a ‘test’ database. You could run queries like this (running on Pubs as reference, and PubsBuild as the development database):

You can occasionally get false alerts based on the way that the various datatypes are rendered in JSON (CHAR(n) datatypes have trailing spaces retained). All you need to do is specify every column and convert to the correct datatype if this happens.

We want to do better than this, because we want to know, if there is a problem, where it failed, the row and column. We need to use SQL Data Compare to do this!

Open it up and create a new project. In this case my source is called reference, where I created the views based on the output from the procedures in Pubs, and the target is called TestSPs, where I created the equivalent views based on the output from the current development build (remember that in the later I’d split the table on which the procedures depended and altered the stored procedures as a result):

Before you run the comparison, you’ll need to go to the Options tab and make sure that you include views, and trim trailing white space because I’ve changed one of the columns from a CHAR(nnn) string to an integer:

Next, go to the Tables&Views tab and select the mapping key(s) for each table; you have the RowNumber column to fall back on if necessary. You are now ready to do the comparison to make sure the results are the same and, in this case, we find that they are identical:

Just to show that it can pick up an error, we maliciously insert an extra row into the TagTitle column in our development build, and rerun the comparison:

We can see precisely where the problem is: there are extra rows generated for ReptQ2Pubs and ReptQ3Pubs because we’ve introduced two primary tags for one title, though an unsuspecting person might take some time to figure out how it happened.

A word of caution on using OPENROWSET (or OPENDATASOURCE)

The two “routine views” databases we’ve created have connection information in them and if you are using SQL Server authentication, the credentials are in plain sight in the metadata and the script. There is no easy way around this because the connection string parameter is a string literal and cannot be a variable. Where possible, it is much better to use windows authentication for this testing.

You can call stored procedures with OPENQUERY with a linked server without any problems except that you cannot link with a local server. This would require you to run the tests on a remote server that has set up the other server(s) as linked servers, which could easily be a complication.

Otherwise, storing these scripts in source control can be a problem unless the actual credentials are completely masked immediately after use, and before checking them in to version control. The test databases must be deleted after the tests are completed.


It might seem quite an overhead to have two extra databases just so you can test the procedures and functions of your development database out more easily against a reference version.

However, once you have them, they only change with changes in procedures or table functions. They make the testing process so much easier that it never seems an effort to keep it up to date. Also, you can go beyond just testing the results of your procedures or functions against the equivalent results of a reference server. You will soon find that you can test procedures or functions against views containing static data held in multi-column SQL statements, or JSON. This idea grows with your imagination, and because it makes testing so easy, it gets used. SQL Data Compare becomes an even more useful tool for the database developer.

Tools in this post

SQL Data Compare

Compare and synchronize SQL Server database contents

Find out more