How to write test cases for testing databases, especially for testing and writing test cases for stored procedures. any real time database testers. please answer me.
Printable View
How to write test cases for testing databases, especially for testing and writing test cases for stored procedures. any real time database testers. please answer me.
The Stored Procedure to Test
In an ad hoc production environment you might just create your back-end database on the fly with the Enterprise Manager program. But it is much better to write a SQL database creation script so you can recreate the database whenever you need to. (Although you can generate database creation scripts in Enterprise Manager from existing databases, the resulting code is usually clunky.) The script in Figure 3 creates a database named dbProseware, then creates a table named tblEmployees, and a user-defined stored procedure called usp_empStatusCode.
After checking to make sure that the database doesn't already exist, I create a database named dbProseware and a table named tblEmployees. Next, I insert some developer data—data used during the development process to be able to perform some basic functionality testing.
The last part of the script creates the stored procedure, usp_empStatusCode, that we'll be testing. It accepts an employee ID and returns 0 if the ID is not valid, 1 if the employee has any termination date (meaning they are not currently employed), 2 if the employee was hired before January 1, 2000, and 3 if the employee was hired after January 1, 2000. Note that this stored procedure is not necessarily an example of good coding technique, but I wanted to keep the code short.
If your background is primarily in procedural programming, you probably tend to think of SQL stored procedures as quite a bit like functions in a traditional programming language. But SQL stored procedures are significantly different from regular functions because in most cases they have a logical dependency on a table or other database object. In this example, notice that the return value from the usp_empStatusCode stored procedure depends completely on the data in table tblEmployees. This fact makes testing SQL stored procedures somewhat different from testing regular functions.
Can you help me write test cases?