Power of Stored procedure as an abstract layer in Microsoft SQL Server 2005

In the way of being better developer, I have started these practice couple years back. Abstract layer is nothing than the entry point of the database. No orphan DML statement allowed. Everything should be done with SPs. By this way, you can have more secure way of dealing with database along with very good performance. It increases encapsulation as well.

One more benefit is by keeping your data access contract as it is; you can improve your database schema and server side code. In one of my past company, I have started working in one application which was already developed with one database, I found many correction could be done in database side but I wasn’t able to do so, just because they have directly used SELECT, INSERT and UPDATE command all over the place so any single change in table structure or in database schema could make application running stop. If they would have used abstract layer and would have done everything with SPs, I would have made necessary change without affecting application.

Data abstract layer will need for every operation we could done with database, like INSERT, UPDATE, DELETE, Fetching record sets or single row etc.

You might think that if we would have to create data abstract layer for everything than why shouldn’t we create INSERT, UPDATE, DELETE and SELECT SPs for every table while creating it, I would not suggest it.

Let us see one sample example how can we do this?

I will explain how to create data abstract layer for INSERT data.

--table for demo

CREATE TABLE AbstractExample

(

ID INT IDENTITY(1,1),

Name VARCHAR(50) NOT NULL,

Company VARCHAR(50) NOT NULL

)

--CREATE data abstract interface for INSERT

CREATE PROC Insert_AbsExample(@Name VARCHAR(50),@Company VARCHAR(50))

AS

SET NOCOUNT ON

BEGIN

IF @Name IS NULL

RAISERROR('Name should be provided',15,1)

IF LEN(RTRIM(@Name))<=0

RAISERROR('only space is not allowed in Name',16,1)

IF @Company IS NULL

RAISERROR('Company Name should be provided',15,1)

IF LEN(RTRIM(@Company))<=0

RAISERROR('only space is not allowed in Company Name',16,1)

BEGIN TRY

INSERT INTO AbstractExample (Name,Company) VALUES(@Name,@Company)

END TRY

BEGIN CATCH

RAISERROR('Unable to insert the records',15,1)

END CATCH

END

--let us check functionality

EXEC Insert_AbsExample ' ','SQLHub'

--let us check functionality

EXEC Insert_AbsExample NULL,'SQLHub'

--let us check functionality

EXEC Insert_AbsExample 'Ritesh','SQLHub'

--check the table.

SELECT * FROM AbstractExample

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com