Validating sql stored procedures

A stored procedure is a named set of PL/SQL statements designed to perform an action. They define a programming interface for the database rather than allowing the client application to interact with database objects directly.

validating sql stored procedures-28validating sql stored procedures-42

For example, we could add validation rules to enforce that the Customer's phone number follows a valid phone pattern, and that we don't add Orders where the customer's Required Date for delivery is before the actual Order Date of the Order.In today's blog post I'm going to discuss how you can also optionally use SPROCs to update/insert/delete data from the database.To help illustrate this - let's start from scratch and build-up a data access layer for the Northwind sample database: In my Part 2: Defining our Data Model Classes tutorial I discussed how to use the LINQ to SQL ORM designer that is built-in to VS 2008 to create a LINQ to SQL class model like below: After defining our data model classes and relationships we'll want to add some business logic validation to our data model.Language specific recommendations: In rare circumstances, prepared statements can harm performance.When confronted with this situation, it is best to either a) strongly validate all data or b) escape all user supplied input using an escaping routine specific to your database vendor as described below, rather than using a prepared statement.Parameterized queries force the developer to first define all the SQL code, and then pass in each parameter to the query later.This coding style allows the database to distinguish between code and data, regardless of what user input is supplied.Are you sure your T-SQL code will stand up to users' demands?What about the functionality you've designed into your code? Does each function deliver as advertised under all normal operating scenarios? If you find yourself falling short in any of these areas, you might want to reassess the way you unit-test your stored procedures.Over the last few weeks I've been writing a series of blog posts that cover LINQ to SQL.LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .

You must have an account to comment. Please register or login here!