{"id":298,"date":"2007-08-20T00:00:00","date_gmt":"2007-08-20T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/close-those-loopholes-testing-stored-procedures\/"},"modified":"2021-09-29T16:22:16","modified_gmt":"2021-09-29T16:22:16","slug":"close-those-loopholes-testing-stored-procedures","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/close-those-loopholes-testing-stored-procedures\/","title":{"rendered":"Close those Loopholes – Testing Stored Procedures"},"content":{"rendered":"
\n

AK:<\/p>\n

In the previous article, Close those Loopholes in your Database Testing<\/a>, I was discussing database testing in general. In this article Alex Styler and I will concentrate on database unit testing. Unit testing is becoming more and more common among C#, C++, and Java programmers, because it is very important in ensuring high quality of software. In the following article we shall demonstrate a simple way to test stored procedures that return result sets. In the next one we shall discuss a few more complex techniques used in testing database modifications.<\/p>\n

Why use C# and NUnit for database unit testing?<\/h3>\n

AK:<\/p>\n

Although there are alternatives, such as Team Edition for Database Professionals and TSQLUnit, the choice of C# and NUnit was natural for me. The reason is simple: being an old hand, I wanted to go for a commonly used, well-known, widespread approach. This is where C# fits the bill perfectly – in Windows programming universe C# is as common as it goes. And NUnit is a very commonly used library for development of unit tests in C# – it has been around for quite a while too. More to the point, if you are working with SQL Server, you are quite likely to be already familiar with C# or at least Visual Studio.<\/p>\n

I am primarily a database developer. As such, I certainly prefer doing things in the database. For example, I am used to writing tests for my stored procedures in T-SQL – I have been doing that for years. However, I think that Visual Studio and NUnit are much better tools for the tedious task of thorough testing. Even though I am much more proficient in Transact SQL than in C#, I definitely prefer to write unit tests for my stored procedures using C# and NUnit.<\/p>\n

Creating a unit test<\/h3>\n

AK:<\/p>\n

Consider the following table, initial data, and existing stored procedure:<\/p>\n

CREATE\u00a0TABLE\u00a0data.Employees(
\n\u00a0\u00a0EmployeeID\u00a0INT\u00a0NOT\u00a0NULL,
\n\u00a0\u00a0FirstName\u00a0VARCHAR(20)\u00a0NOT\u00a0NULL,\u00a0
\n\u00a0\u00a0LastName\u00a0VARCHAR(20)\u00a0NOT\u00a0NULL,
\n\u00a0\u00a0Status\u00a0VARCHAR(6)\u00a0NOT\u00a0NULL\u00a0CONSTRAINT\u00a0CHK_Employees_Status\u00a0CHECK(Status\u00a0IN('Active',\u00a0'Quit')),
\n\u00a0\u00a0HireDate\u00a0DATETIME\u00a0NOT\u00a0NULL
\n)
\nGO <\/p>\n

INSERT\u00a0INTO\u00a0data.Employees(EmployeeID,\u00a0FirstName,\u00a0LastName,\u00a0Status,\u00a0HireDate)\u00a0VALUES(1,\u00a0'Sean',\u00a0'Hansen',\u00a0'Active',\u00a0'20070105')
\nINSERT\u00a0INTO\u00a0data.Employees(EmployeeID,\u00a0FirstName,\u00a0LastName,\u00a0Status,\u00a0HireDate)\u00a0VALUES(2,\u00a0'Don',\u00a0'Wang',\u00a0'Active',\u00a0'20070106')
\nGO <\/p>\n

CREATE\u00a0PROCEDURE\u00a0Readers.SelectEmployeesByLastName
\n\u00a0\u00a0@LastName\u00a0VARCHAR(20)
\nAS
\nSELECT\u00a0*\u00a0FROM\u00a0data.Employees\u00a0WHERE\u00a0LastName\u00a0=\u00a0@LastName
\nORDER\u00a0BY\u00a0FirstName
\nGO
\n<\/code><\/p>\n

Suppose you need to write a unit test for that stored procedure. You might want to verify that your result set has correct structure (correct column names and types) and that is contains correct data. Because this is a very common task, it definitely makes sense to implement all these comparisons once, in a C# class, and use the library many times. For any reasonable project you will have at least hundreds of such tests, so implementing the comparison once in a class is definitely worth the effort. Once all the comparison logic has been implemented, your typical unit test is very short and simple:<\/p>\n

private const bool bSetupMode = true;
\n\u00a0
\n[Test]
\npublic void SelectEmployeesByLastName_Test()
\n{
\n\u00a0\u00a0 string filename = “C:\/Temp\/SelectEmployeesByLastNameTest.xml”;
\n\u00a0\u00a0 StoredProcedureTester tester = new
\n\u00a0\u00a0 StoredProcedureTester( connection, SelectEmployeesByLastName” );
\n\u00a0\u00a0
\n\u00a0\u00a0 tester.SetInputParameter( “@lastname”, “Hansen” );
\n\u00a0\u00a0\u00a0
\n\u00a0\u00a0 if (bSetupMode)
\n\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 tester.OutputToFile( filename );
\n\u00a0\u00a0\u00a0 }
\n\u00a0\u00a0\u00a0 else
\n\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0 Assert.IsTrue(tester.CompareToFile( filename ));
\n\u00a0\u00a0\u00a0 }
\n}<\/p>\n

When you set up your unit test, your result set (or result sets, if your stored procedure returns several ones) is saved into an XML file. All the information necessary for further comparison is saved – column names, column types, and the data. The XML file needs to be checked into your source control system, because it is part of your test harness. When you run your unit test, the result set is compared against the saved XML file. Everything is compared – column names, column types, and all the data Both OutputToFile and CompareToFile are methods of StoredProcedureTester class. We shall provide the implementation of the class in the next chapters.<\/p>\n

Unit testing is not a substitute for other types of testing.<\/h3>\n

AK:<\/p>\n

Unit tests by definition must run consistently, always producing the same results. As such, they do not expose all the problems. For instance, in some cases concurrency may affect your selects – they may run perfectly in a single-connection test environment but give you problems in a multi-user production one. For example, you can silently get incorrect results because of your choice of isolation level. One possible scenario is described in this article:
\nWhen Snapshot Isolation Helps and When It Hurts<\/a><\/p>\n

In some cases a select can even blow up because of concurrency, as described in the following excellent blog entry in Craig Freedman’s WebLog: Query Failure with Read Uncommitted<\/a><\/p>\n

Unfortunately, problems caused by concurrency may be intermittent – sometimes they can be reproduced, but not in exactly the same way every time you run your tests. In some cases if you stress test your selects properly, and your selects have problems, then your stress testing is likely to expose these problems sooner or later. This is why exposing problems in your selects caused by concurrency, however important it is, is mostly beyond the scope of unit testing and this article.<\/p>\n

The Result Set Tester<\/h3>\n

AK:<\/p>\n

In the “Creating Unit Test” chapter I utilized StoredProcedureTester class. In this and the next chapter AS will demonstrate how to implement this class. The following implementation <\/a>has been stripped of all the bells and whistles – we left just enough code to demonstrate the technique in its simplest form. For example, a fully functional library should be able to compare result sets that are ordered on a non-unique column, such as FirstName, in one of the preceding examples. While our actual library can do all this and more, our example has been stripped of all this functionality.<\/p>\n

AS:<\/p>\n

We included a more powerful version of the testing library in the attached archive. The archive version abandons datasets in favor of speed and greater control over data comparisons. It might be a little harder to understand at first, but if you need greater control and flexibility, it might be worth your time to check it out.<\/p>\n

Behind a seemingly simple unit test are the classes necessary to compare the result sets of your procedures. The first class, ResultSetTester<\/b>, acts as an interface and provides most of the basic functionality for testing. It is constructed with a connection, implying a server and database, and the name of a stored procedure. You can then supply input and output parameters and either save the results to a file or compare the results to a saved file. Output parameters must be specified with a type and size and can be fetched after the procedure has run.<\/p>\n

using System;
\nusing System.Collections.Generic;
\nusing System.Data;
\nusing System.Data.SqlClient;
\n\u00a0
\nnamespace UnitTestingTools
\n{
\n\u00a0\u00a0\u00a0 public class ResultSetTester
\n\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 private readonly DataSet results = new DataSet();
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 private readonly SqlCommand storedProcedure;
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public ResultSetTester(SqlConnection connection, string procedureName)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 storedProcedure = new SqlCommand(procedureName, connection);
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 storedProcedure.CommandType = CommandType.StoredProcedure;
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public bool CompareToFile(string filename)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RunProcedure();
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 DataSet other = new DataSet();
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 other.ReadXml(filename);
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 other.AcceptChanges();
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return DataSetComparer.Compare(results, other);
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public void OutputToFile(string filename)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 RunProcedure();
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 results.WriteXml(filename, XmlWriteMode.WriteSchema);
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public IEnumerable<KeyValuePair<string, object>> OuputParameters
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 get
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dictionary<string, object> outputParams = new Dictionary<string, object>();
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 foreach(SqlParameter p in storedProcedure.Parameters)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if(p.Direction == ParameterDirection.Output)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 outputParams.Add(p.ParameterName, p.Value);
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return outputParams;
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public void SetInputParameter(string parameterName, object parameterValue)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SqlParameterCollection parameters = storedProcedure.Parameters;
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if(parameters.Contains(parameterName))
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameters[parameterName] = new SqlParameter(parameterName, parameterValue);
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 else
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameters.AddWithValue(parameterName, parameterValue);
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 public void SetOutputParameter(string parameterName, SqlDbType type, int size)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SqlParameterCollection parameters = storedProcedure.Parameters;
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if(parameters.Contains(parameterName))
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameters.RemoveAt(parameterName);
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameters.Add(parameterName, type);
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameters[parameterName].Direction = ParameterDirection.Output;
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if(size > 0)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 parameters[parameterName].Size = size;
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 private void RunProcedure()
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 results.Reset();
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 using(SqlDataAdapter da = new SqlDataAdapter(storedProcedure))
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 da.Fill(results);
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for(int i = 0; i < results.Tables.Count; i++)
\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0results.Tables[i].TableName = “Result Set ” + i;
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 results.AcceptChanges();
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\n\u00a0\u00a0\u00a0 }
\n}<\/p>\n

The DataSet Comparer<\/h3>\n

AS:<\/p>\n

One of the most powerful of the ADO.NET objects is the DataSet<\/b>. It is used in this example due to the ease of fetching results and the simplicity of storing the results in a file. However, due to varied opinions among developers on what defines the equality of data, the DataSet<\/b> provides no means for data comparison.<\/p>\n

This, however, allows you to define a means to compare data, directly determining how result sets are compared. In the example class below, DataSetComparer<\/b>, the data must be exactly the same with the same ordering of result sets, rows, and columns. This works for all common data types, but would need custom code to work with user defined types or SqlVariant<\/b> types that cannot be converted to strings. You can alter this comparer to ignore certain column types, allow varying order of rows, or compare only general trends of the data to fit your needs.<\/p>\n

using System;
\nusing System.Data;
\n\u00a0
\nnamespace UnitTestingTools
\n{
\n\u00a0\u00a0 internal static class DataSetComparer
\n\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0 internal static bool Compare(DataSet one, DataSet two)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if(one.Tables.Count != two.Tables.Count)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return false;
\n\u00a0
\n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0for(int i = 0; i < one.Tables.Count; i++)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if(!CompareTables(one.Tables[i], two.Tables[i]))
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return false;
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return true;
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0 private static bool CompareTables(DataTable one, DataTable two)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0if(one.Rows.Count != two.Rows.Count)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return false;
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for(int i = 0; i < one.Rows.Count; i++)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if(!CompareRows(one.Rows[i], two.Rows[i]))
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return false;
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return true;
\n\u00a0\u00a0\u00a0\u00a0\u00a0 }
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0 private static bool CompareRows(DataRow one, DataRow two)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if(one.ItemArray.Length != two.ItemArray.Length)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return false;
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for(int i = 0; i < one.ItemArray.Length; i++)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if(!CompareItems(one.ItemArray[i], two.ItemArray[i]))
\n\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0return false;
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return true;
\n\u00a0\u00a0\u00a0\u00a0\u00a0 }
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0 private static bool CompareItems(object value1, object value2)
\n\u00a0\u00a0\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if(value1.GetType() != value2.GetType())
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return false;
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if(value1 is DBNull)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return true;
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if(value1 is DateTime)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return ((DateTime) value1).CompareTo((DateTime) value2)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 == 0;
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if(value1 is byte[])
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 {
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if(((byte[]) value1).Length != ((byte[]) value2).Length)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return false;
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 for(int i = 0; i < ((byte[]) value1).Length; i++)
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 if(((byte[]) value1)[i] != ((byte[]) value2)[i])
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return false;
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return true;
\n\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0}
\n\u00a0
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 return value1.ToString().Equals(value2.ToString());
\n\u00a0\u00a0\u00a0\u00a0\u00a0 }
\n\u00a0\u00a0 }<\/p>\n

}<\/p>\n

Conclusions<\/h3>\n

AS:<\/p>\n

If you are using C# and NUnit, you already have all the necessary tools to start covering your stored procedures with unit tests. Good luck!<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Alex and Alex continue their series of articles on ‘Unit Testing’ database development work with some examples of unit testing stored procedures.…<\/p>\n","protected":false},"author":14235,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4168,4150,4252,4815],"coauthors":[48777],"class_list":["post-298","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-database","tag-sql","tag-t-sql-programming","tag-unit-test-nunit-stored-procedure"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/298","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/14235"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=298"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/298\/revisions"}],"predecessor-version":[{"id":74430,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/298\/revisions\/74430"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=298"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=298"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=298"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=298"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}