Goal:
Make lots of different test for a specific stored procedure named 'sp_test' in order to review if you are retrieving the same data based on data input.
Purpose:
If you have made lots of changes in the stored stored 'sp_test' and you need to make validation if this SP works perfectly before using it in production phase. I need to make a lot of test. There are at least 20 different test. The code below is a simplified version.
Problem:
Do you have a recommended solution or approach how to create it? My request is to retrieve a table that display the column testnumber, Parameter and Result. (The column Result displays 'Passed' or Failed).
The testlist is below.
I see this approach the same way as TDD.
Thanks!
Test number 1:
Parameter 'Science'
Hit: 5 rows
Hit is the correct answer from the SP
Test number 2:
Parameter 'Programming'
Hit: 1 row
Hit is the correct answer from the SP
Test number 3:
Parameter 'Computers'
Hit: 2 row
Hit is the correct answer from the SP
Test number 4:
Parameter 'Business'
Hit: 1 row and BookID = 6
Hit 1 is correct and then review if BookID is 6 are correct answer from SP
CREATE TABLE [dbo].[Books](
[BookID] [int] IDENTITY(1,1) NOT NULL,
[BookName] [varchar](50) NULL,
[Category] [varchar](50) NULL,
[Price] [numeric](18, 2) NULL,
[Price_Range] [varchar](20) NULL,
PRIMARY KEY CLUSTERED ( [BookID] ASC )
) ON [PRIMARY]
INSERT INTO dbo.Books
(BookName, Category, Price, Price_Range)
VALUES
('Computer Architecture', 'Computers', 125.6, '100-150'),
('Advanced Composite Materials', 'Science', 172.56, '150-200'),
('Asp.Net 4 Blue Book', 'Programming', 56.00, '50-100'),
('Strategies Unplugged', 'Science', 99.99, '50-100'),
('Teaching Science', 'Science', 164.10, '150-200'),
('Challenging Times', 'Business', 150.70, '150-200'),
('Circuit Bending', 'Science', 112.00, '100-150'),
('Popular Science', 'Science', 210.40, '200-250'),
('ADOBE Premiere', 'Computers', 62.20, '50-100')
CREATE PROCEDURE sp_test
@test nvarchar(50)
AS
SET NOCOUNT ON;
SELECT
[BookID],
[BookName],
[Category],
[Price],
[Price_Range]
FROM [dbo].[Books]
WHERE [Category] = @test
GO