Database testing includes performing data validity, data integrity testing, performance check related to database and testing of procedures, triggers and functions in the database.Consider an application that captures the day-to-day transaction details for users and stores the details in the database.Dwain explains how it was done, and provides a template.It is not a trivial matter to build the sort of multi-tier, web application that every corporation seems to want.When faced with a complex business application that had to be delivered with minimum staffing, on-time and within budget, Dwain's team chose to encapsulate and implement most of an application's business logic in SQL Server, using an interface made up of stored procedures.Without this approach, the team was convinced that it would not have been possible to deliver that level of business logic complexity within the timeframe.One method of creating multiple lines of defense around your database is to implement all data access using stored procedures or user-defined functions.
T-SQL Stored Procedures (SPs), along with some views and functions, are a useful way to encapsulate and implement most of an application’s business logic, especially that which retrieves the underlying data from the tables (master or transaction), and/or updates it.
In this article, we will teach how to create stored procedures and functions in SQL Server and show advantages and disadvantages one of each.
In our examples, we will use scalar user defined functions aka UDFs.
Without going too far into the details, I can say that in my 7 years of experience with this company, this application ranks within the top three or four in complexity among all of those I’ve been intimately involved with.
It was the culmination of refining the approach I will describe, and it was successful because it was delivered with minimum staffing, on-time and within budget.