Part 2: Why Should Testers Know SQL?

SQL Tester Tim

In my last blog, we looked at a scenario where a junior tester needed to write some SQL to quickly change the administration rights on a test user.

Here, we will look at a scenario a Senior Tester might see during a project. 

 

Tester Level: Senior

Scenario:   

A database driven Windows application has been architected to not crash when there is a system exception (error). It is supposed to handle these errors gracefully by writing them to a custom error log and activating a warning icon on its ribbon. One column in the error record called “Layer” must be set to one of these values- UI, Logic, or Data. There is a window in the application that displays the error log. Test cases have been written to validate that errors caught in any of the three layers are correctly logged.    

 

Tester Tim know he can’t just yank his computer’s internet connection at random times to cause an exception to be thrown, so he needs a plan to be able to make exceptions happen on demand in the database.   

 

Resolution:

Tester Tim does some research on Transact-SQL exceptions. (BTW, Transact-SQL or T-SQL is Microsoft’s implementation of SQL with a set of additional functions.) He sees a list of some common computer science coding errors that throw exceptions like using a variable that is set to null at the wrong time or dividing a number by zero. He also sees a lot of references to the RaiseError and Throw commands. It looks like the RaiseError command is no longer recommended, so he studies examples of the THROW command.    

 

Tester Tim knows that the application uses stored procedures (stored procs) to execute its SQL. A stored procedure allows a programmer to encapsulate a set of SQL commands into a callable function. He uses his SQL client to see all the stored procedures and notices that there are Create, Read, Update and Delete (CRUD) stored procedures for almost all the entities in the application. He views the Stored Procedure called spReadUser and sees the following:

 

CREATE OR ALTER PROCEDURE spReadUser

@UserID int

AS

SELECT

u.ID, u.FullName, u.EmailAddress, u.DateCreated,

FROM

[User] u

WHERE

u.ID = @UserID

 

Tester Tim knows that anything that starts with a @ is either an argument or a variable in Transact SQL. This stored proc has one argument that is an integer and returns a record set with four columns. He investigates the schema of the User table and sees that the ID is the primary key, so he knows that the returned record set will either have 0 or 1 rows. How can he force a simple select statement to throw an exception?    

 

Let’s modify this for my test, and make a classic computer science mistake on purpose! I see an integer parameter, so let’s do some illegal arithmetic with it. 

 

First, he writes a free standing SQL test and executes it in his SQL admin tool to make sure it actually causes an exception:

 

declare @userID as integer
set @userID=10;

declare @quotient as integer
set @quotient = @userID/0;    

 

He is happy when he sees the following message appear: 

 

Msg 8134, Level 16, State 1, Line 6

Divide by zero error encountered.

 

Now it’s time to modify the “live” stored proc used in the application with this logic. Tester Tim makes a backup copy of the existing stored proc and then writes and executes the following replacement code: 

 

CREATE OR ALTER PROCEDURE spReadUser

@UserID int

AS

       — START OFTEST CODE (MUST BE REMOVED AFTER TEST) 

       — ADDED FOR TEST CASE 76 – EXCEPTION HANDLING

declare @quotient as integer

set @quotient = @UserID/0;      

       — END OF TEST CODE

SELECT

u.ID,

u.FullName,

u.EmailAddress,

u.DateCreated,

FROM

[User] u

WHERE

u.ID = @UserID

 

He opens the website to the page (view) that he knows executes the modified stored procedure and runs his exception handling test case. 

 

Tester Tim wants a little more control over the error message, error id, and error state thrown. He decides to test the THROW command in his SQL client. He reviews the syntax of the command on the Microsoft website https://docs.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql?view=sql-server-ver15  and gives it a try: 

 

THROW 123456, ‘Tester Tim caused this exception’, 7;

The following error message appears.

Msg 123456, Level 16, State 7, Line 8

Tester Tim caused this exception

 

That worked! Tester Tim alters the stored proc, and replaces the divided by zero logic with the THROW command. 

Tester Tim Success

 

CREATE OR ALTER PROCEDURE spReadUser

@UserID int

AS

       — START OF TEST CODE (MUST BE REMOVED AFTER TEST) 

       — ADDED FOR TEST CASE 76 – EXCEPTION HANDLING

       THROW 123456, ‘Tester Tim caused this exception’, 7;

       — END OF TEST CODE

SELECT

u.ID,

u.FullName,

u.EmailAddress,

u.DateCreated,

FROM

[User] u

WHERE

u.ID = @UserID

 

He runs his test case again to see if the application is gracefully handling a thrown exception from the data layer of the application.

 

Tester Tim now has a reusable solution to cause exceptions on demand in any stored procedure that he will use many times on future projects.  

 

I hope that you found this SQL scenario to be a useful illustration, and stay tuned for my final blog in this series.

Dan Hakimian Headshot

Dan Hakimian    


Related Content: Quality & Testing

Don't Miss An Olenick Article!

Subscribe to receive our latest blog articles, right to your inbox.