Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server / SQL Server 2008

SQL Framework

Rate me:
Please Sign up or sign in to vote.
4.73/5 (24 votes)
16 Aug 2012CPOL3 min read 82.7K   474   69   20
Framework makes application more organized and efficient way to coding. It is helpful for newly assigned developer to understand code structure within a short time. It is essential for large application.

Introduction

Developers who have experienced large application development know the necessities of framework. In this regard, more experienced developers like Team Lead, Technical Lead and Project Manager up to CTO have high respect for it. They know from their experience that large applications should develop in a very organized way so that newly assigned developers take little time to adopt the development structure behind the application and take less costly man hour form Team Lead or other senior developers. They also know that maintenance will be easy and cost effective after development has been completed for the application.

Background

Apart from this, I will be going through developing a SQL Framework. I will show the necessity of it with demonstration.

Let’s Get Started

  • Open SQL Management Studio

I want to start by designing a table structure in SQL Management Studio as shown below:

SQLFramework/CustomerTableStructure.JPG

Figure 1

Say you are working in a large software company for a big project and will take a long time. You have many programmers working under you in this project. Sometimes, programmers are switching from your software company and are replaced by new programmers. So far, this is a big project so underlining database size will be accordingly big. Say, you have assigned a task to a programmer to create a stored procedure for data insertion with proper validation and other necessary command in store procedure.

The below procedure has been created by the programmer as his assigned task.

SQL
CREATE PROCEDURE [dbo].[Customer_Set]
	@Name as varchar(50),
    	@Address as varchar(250),    
	@Mobile as varchar(50)
AS
BEGIN

	SET NOCOUNT ON

	--Validation
	IF @Name IS NULL
	BEGIN
		RAISERROR ('Name cannot be empty.',16,1)
	END

	IF LEN(@Name)<3
	BEGIN
		RAISERROR ('Name cannot be less than 3 characters.',16,1)
	END	

	--Data Insertion
	BEGIN TRY
		INSERT INTO [dbo].[Customer]
			([Name]
			,[Address]
			,[Mobile])
		VALUES
			(@Name
			,@Address			
			,@Mobile)
	END TRY
	BEGIN CATCH
		RETURN (0)
	END CATCH

 RETURN (1)
END

You have also assigned another developer to write a stored procedure for supplier table.

SQLFramework/SupplierTableStructure.JPG

Figure 2

Then write stored procedure like below:

SQL
CREATE PROCEDURE [dbo].[Supplier_Set]
@Name as varchar(50),
@Address as varchar(250),    
@Mobile as varchar(50)
AS
BEGIN

	SET NOCOUNT ON

	--Validation
	IF @Name IS NULL
	BEGIN
		RAISERROR ('Please enter supplier name.',16,1)
	END

	IF LEN(@Name)<3
	BEGIN
		RAISERROR ('Supplier name cannot be less than 3 characters.',16,1)
	END	

	--Data Insertion
	BEGIN TRY
		INSERT INTO [dbo].[Supplier]
			([Name]
			,[Address]
			,[Mobile])
		VALUES
			(@Name
			,@Address			
			,@Mobile)
	END TRY
	BEGIN CATCH
		RETURN (-1)
	END CATCH

 RETURN (1)
END

While reviewing code for the above two stored procedures written by two programmers, you will find the following difference:

SQL
IF @Name IS NULL
BEGIN
RAISERROR ('Name cannot be empty.',16,1)
END

IF LEN(@Name)<3
BEGIN
RAISERROR ('Name cannot be less than 3 characters.',16,1)
END	

BEGIN CATCH
		RETURN (0)
	END CATCH
 RETURN (1)

Another programmer:

SQL
IF @Name IS NULL
BEGIN
RAISERROR ('Please enter supplier name.',16,1)
END

IF LEN(@Name)<3
BEGIN
RAISERROR ('Supplier name cannot be less than 3 characters.',16,1)
END	

BEGIN CATCH
		RETURN (-1)
	END CATCH

 RETURN (1)

So one programmer writes:

  1. Name cannot be empty
  2. Name cannot be less than 3 characters
  3. RETURN (0)

Another programmer writes:

  1. Please enter supplier name
  2. Supplier name cannot be less than 3 characters
  3. RETURN (-1)

For the same kind of work, they have not used similar messages and they have also return 0 and -1 which are not understandable. And other developers may write something other than this. So this system is not a generic system or not followed by the same structure.

It will be very tough to manage if you have a large number of stored procedures like the above written by many developers.

System should develop followed by a structure that I called framework and all messages should have a common, return type that should be readable and other things regarding a structure should be common. That will help us to manage system easily and by using less effort.

We can build a framework by writing some additional stored procedures in this case and you can increase as your requirement to build your own.

Let's create a stored procedure to set some constant variables most likely as Enum in .NET. These constants will actually be used in other regular procedure for different type of operation. These constants are for common system development.

SQL
CREATE PROCEDURE [dbo].[GetValidationConstraint]
	--Output values
	@EmptyCheck int OUTPUT,
	@LenCheck int  =NULL OUTPUT,
	@NoDataExist int =NULL OUTPUT,
	@True bit =NULL OUTPUT,	
	@False bit =NULL OUTPUT
AS
BEGIN
	SELECT @EmptyCheck=1
	SELECT @LenCheck =2
	SELECT @NoDataExist =3

	SELECT @True=1	
	SELECT @False=0
END

Another procedure is for common return message for the whole application.

SQL
CREATE PROCEDURE [dbo].[ReturnMessage]
	--Success, Fail is the order of output parameter
	@Success int OUTPUT,	
	@Fail int OUTPUT	
AS
SET NOCOUNT ON

BEGIN
	SELECT @Fail=0
	SELECT @Success=1		
END

The below procedure is for common messages for the application:

SQL
CREATE PROCEDURE [dbo].[MessageHelper]
	--Input values
	@Field varchar(200) =NULL,
	@MinLenght int =NULL,
	@ValidationConstraint int,
	--Output values
	@ValidationMessage varchar(200) OUTPUT	
AS
BEGIN
	--Variables
	DECLARE @EMPTY_MESSAGE varchar(50),
		@MINIMUM_LENGHT_MESSAGE varchar(50),
		@NO_DATA_EXIST_MESSAGE varchar(50)

	DECLARE @EMPTY int,
		@LEN int,
		@NO_DATA_EXIST int	

	DECLARE @SUCCESSED int,
		@FAILED int	

	--Message Constraint
	SET @EMPTY_MESSAGE = 'cannot be empty.'
	SET @MINIMUM_LENGHT_MESSAGE ='cannot be less than'
	SET @NO_DATA_EXIST_MESSAGE = 'No record found.'
	
	--Get global values
	EXEC ReturnMessage @SUCCESSED output, @FAILED output
	EXEC GetValidationConstraint @EMPTY OUTPUT, @LEN OUTPUT, @NO_DATA_EXIST OUTPUT

	--Set message
	IF @ValidationConstraint = @EMPTY 
	BEGIN
		IF LEN(@Field)<=0
		BEGIN
			RAISERROR('Field name cannot be empty. _
				StoreProcedure/MessageHelper',16,1)	  	
			RETURN @FAILED
		END
		SELECT @ValidationMessage = @Field + ' ' + @EMPTY_MESSAGE	
	END

	IF @ValidationConstraint = @LEN
	BEGIN
		IF @MinLenght IS NULL OR @MinLenght <=0
		BEGIN
			RAISERROR('Minimum length cannot be empty. _
				StoreProcedure/MessageHelper',16,1)	  	
			RETURN @FAILED
		END
		ELSE
		BEGIN
			SELECT @ValidationMessage = @Field + ' ' + _
			@MINIMUM_LENGHT_MESSAGE + ' ' + CONVERT(varchar, @MinLenght)
		END
	END

	IF @ValidationConstraint = @NO_DATA_EXIST 
	BEGIN	  	
		SELECT @ValidationMessage = @NO_DATA_EXIST_MESSAGE			
	END
END

Write your procedure for insert data into Customer table:

SQL
CREATE PROCEDURE [dbo].[Customer_Set]
	--Input values
	@Name as varchar(50),
    @Address as varchar(250),    
	@Mobile as varchar(50),
	--Output values
	@LASTID bigint OUTPUT,
	@MESSAGE varchar(200) =NULL OUTPUT
AS
SET NOCOUNT ON
BEGIN
	--Constraint Variables For Readable Return Value  
	DECLARE @SUCCESSED int,
		@FAILED int			

	--Constraint Variables For Readable Validation Operation  
	DECLARE @EMPTY int,
		@LEN int		
	
	BEGIN TRY
		--Get constraint value for success and failed
		EXEC ReturnMessage @SUCCESSED output, @FAILED output		
		--Get constraint value for validation. @EMPTY is for empty check 
                   --and @LEN is for length check common messaging system.
		EXEC GetValidationConstraint @EMPTY output, @LEN output
	
		--Validation
		IF LEN(@Name)=0
		BEGIN			
			EXEC MessageHelper 'Name', @EMPTY,@MESSAGE OUTPUT 
                                    --It will generate a common empty message.
			RETURN @FAILED-- Readable Failed Return
		END

		IF LEN(@Name)<3
		BEGIN			
			EXEC MessageHelper 'Name',3, @LEN,@MESSAGE OUTPUT 
                                  --It will generate a common length check message.
			RETURN @FAILED-- Readable Failed Return
		END
		
		--Data insertion
		INSERT INTO [dbo].[Customer]
           ([Name]
           ,[Address]
           ,[Mobile])
		VALUES
           (@Name
           ,@Address
           ,@Mobile)
			
			SELECT @LASTID=SCOPE_IDENTITY()
	END TRY
	BEGIN CATCH -- Error Trapping Section
		DECLARE @ErrorMessage nvarchar(4000);
		DECLARE @ErrorSeverity int;
		DECLARE @ErrorState int;
		
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();
		
		RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
		RETURN @FAILED  -- Readable Failed Return
	END CATCH

	RETURN @SUCCESSED -- Readable Success Return
END

In the above way, all messages in a system will remain common. Now write stored procedure for getting all records from Customer table.

SQL
CREATE PROCEDURE [dbo].[Customer_Get]	
	--Output values	
	@TOTAL_ROWS bigint OUTPUT,	
	@MESSAGE varchar(200) =NULL OUTPUT
	
AS
BEGIN
	SET NOCOUNT ON
	--Variables
	DECLARE @SUCCESSED int,
		@FAILED int		

	DECLARE @EMPTY int,
		@LEN int,
		@NO_DATA_EXIST int	
	
	BEGIN TRY
		--Get constraint value
		EXEC ReturnMessage @SUCCESSED OUTPUT, @FAILED OUTPUT		
		EXEC GetValidationConstraint @EMPTY OUTPUT, _
			@LEN OUTPUT,@NO_DATA_EXIST OUTPUT
	
		--Validation
		IF (SELECT COUNT(CustomerId) FROM  Customer )<= 0 
		BEGIN			
			EXEC MessageHelper '', @NO_DATA_EXIST,@MESSAGE OUTPUT 
				--It will generate common no data exist message.
			SELECT @TOTAL_ROWS=0
			 
			RETURN @SUCCESSED
		END
		
		--Data retrieval	
		SELECT [CustomerId]
		  ,[Name]
		  ,[Address]
		  ,[Mobile]	  
		FROM [dbo].[Customer]		

		--Get total rows	
		SELECT @TOTAL_ROWS=@@ROWCOUNT
		 
	END TRY
	BEGIN CATCH
		DECLARE @ErrorMessage nvarchar(4000);
		DECLARE @ErrorSeverity int;
		DECLARE @ErrorState int;
		
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();		
		
		RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
		RETURN @FAILED
	END CATCH

	RETURN @SUCCESSED
END

Write procedure for delete record from Customer table:

SQL
CREATE  PROCEDURE [dbo].[Customer_DeleteById]
	--Input values
	@CustomerId bigint,	
	@MESSAGE varchar(200) =NULL OUTPUT
AS
BEGIN
	--Variables
	DECLARE @SUCCESSED int,
		@FAILED int			

	DECLARE @EMPTY int,
		@LEN int

	BEGIN TRY
		--Get constraint value
		EXEC ReturnMessage @SUCCESSED OUTPUT, @FAILED OUTPUT		
		EXEC GetValidationConstraint @EMPTY OUTPUT, @LEN OUTPUT
	
		--Validation
		IF @@CustomerId <=0
		BEGIN			
			EXEC MessageHelper 'Customer Id', @EMPTY,@MESSAGE OUTPUT
			RETURN @FAILED
		END
		
		--Data deletion	
		DELETE FROM [dbo].[Customer]
		  WHERE (CustomerId = @CustomerId)

	END TRY
	BEGIN CATCH
		DECLARE @ErrorMessage nvarchar(4000);
		DECLARE @ErrorSeverity int;
		DECLARE @ErrorState int;
		
		SELECT 
			@ErrorMessage = ERROR_MESSAGE(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE();
		
		RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
		RETURN @FAILED
	END CATCH

	RETURN @SUCCESSED
END

So by some additional stored procedures, we can build a common messaging and validation system for web or software applications. By building a common system or framework, we can reduce maintenance time for our software project. And it is easy to understand the whole system.

Conclusion

This demonstration will help you to develop your SQL framework.

Thank You!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Team Leader
Bangladesh Bangladesh
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 3 Pin
nobodyxxxxx16-Aug-12 4:18
nobodyxxxxx16-Aug-12 4:18 
GeneralNice work! Pin
Eddy Vluggen14-Aug-12 22:59
professionalEddy Vluggen14-Aug-12 22:59 
GeneralRe: Nice work! Pin
Abdul Quader Mamun15-Aug-12 1:46
Abdul Quader Mamun15-Aug-12 1:46 
GeneralMy vote of 5 Pin
Wühlmaus14-Aug-12 9:55
Wühlmaus14-Aug-12 9:55 
GeneralMy vote of 5 Pin
crazedDotNetDev14-Aug-12 8:48
crazedDotNetDev14-Aug-12 8:48 
QuestionInteresting Framework But... Pin
kaschimer14-Aug-12 7:51
kaschimer14-Aug-12 7:51 
You have to be careful about how much of your business logic lives in your stored procedures and how much lives where it belongs - in the application.

I've worked on both extremes. One where *all* of the logic lived in stored procedures. Talk about a nightmare to debug. One where there were *no* stored procedures, so any change to the underlying SQL meant a recompile and redploy of the application.

There are also school of thought out there that say avoid stored procedures when possible, and use a ORM to do all of the heavy lifting for you.

Nice article though and good explanation.
Yesterday it worked.

Today it is not working.

Software is like that.


GeneralMy vote of 5 Pin
Christian Amado14-Aug-12 7:37
professionalChristian Amado14-Aug-12 7:37 
GeneralMy vote of 5 Pin
jawed.ace9-May-11 20:51
jawed.ace9-May-11 20:51 
GeneralMy vote of 5 Pin
Аslam Iqbal17-Jan-11 1:31
professionalАslam Iqbal17-Jan-11 1:31 
GeneralRe: My vote of 5 Pin
Abdul Quader Mamun14-Aug-12 15:30
Abdul Quader Mamun14-Aug-12 15:30 
GeneralRe: My vote of 5 Pin
Abdul Quader Mamun14-Aug-12 15:30
Abdul Quader Mamun14-Aug-12 15:30 
GeneralRe: My vote of 5 Pin
Abdul Quader Mamun14-Aug-12 15:30
Abdul Quader Mamun14-Aug-12 15:30 
GeneralMy vote of 5 Pin
SkGyan6-Jan-11 22:35
SkGyan6-Jan-11 22:35 
GeneralMy vote of 3 Pin
fjdiewornncalwe6-Jan-11 5:41
professionalfjdiewornncalwe6-Jan-11 5:41 
GeneralRe: My vote of 3 [modified] Pin
Abdul Quader Mamun9-Jan-11 18:03
Abdul Quader Mamun9-Jan-11 18:03 
GeneralAbout Framework Pin
Md. Rashidul Hasan Masum4-Jan-11 19:23
professionalMd. Rashidul Hasan Masum4-Jan-11 19:23 
GeneralRe: About Framework Pin
Alenty14-Aug-12 16:39
Alenty14-Aug-12 16:39 
Generalthanks for sharing - have 5 Pin
Pranay Rana4-Jan-11 18:49
professionalPranay Rana4-Jan-11 18:49 
GeneralRe: thanks for sharing - have 5 Pin
Abdul Quader Mamun4-Jan-11 19:29
Abdul Quader Mamun4-Jan-11 19:29 
GeneralRe: thanks for sharing - have 5 Pin
Abdul Quader Mamun14-Aug-12 15:29
Abdul Quader Mamun14-Aug-12 15:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.