Stored Procedure

When you want to develop application that has connection with database and need to do basic operation or even you have special logic you want to perform.

You need to specify many points (here what I think is related to DB):

·         How can I connect to my database?

·         Could my database be secure if I use this method or another?

·         How to prevent myself from known types of attack “Refer to my Previous topic about SQL Injection http://elmozamil.blogspot.com/2011/12/normal-0-false-false-false-en-my-x-none.html

After you connect to your database and specify your logic and your needs, you must take into your consideration how to pass values to your database (SQL Injection), authentication and many types. I do not write about this but my main issue here, why should I use Stored Procedure?

What is Stored Procedure?

Store Procedure is a group of Transact-SQL Statements compiled into single execution plan. (This definition from Microsoft MSDN refers to bellow link).

 In stored procedure you can return your output in many forms you can return it as output parameters, or result set.

http://msdn.microsoft.com/en-us/library/aa174792%28v=sql.80%29.aspx

Consider this example

Employee	Salary
Ahmed	4000
Mohammed	10000
Ali	8000

If I want to return the maximum salary I can return it as output parameter or result set:

Output Parameter:

CREATE PROCEDURE procName

@maxSalary INT OUTPUT

AS

BEGIN

                SELECT @maxSalary = MAX(Salary)

FROM TableName

RETURN @maxSalary

END

Result Set:

CREATE PROCEDURE procName

AS

BEGIN

SELECT Employee, MAX(Salary)

From TableName

END

For more information refer to: http://msdn.microsoft.com/en-us/library/aa174792%28v=sql.80%29.aspx

Some examples are more complicated and in this case I like using stored procedure. Think about when you have logic and you may use variables and constants to calculate some value.

Here are points that I think it better to use Store Procedure and use it in your program instead of using select command.

·         To prevent SQL Injection use stored procedure. http://elmozamil.blogspot.com/2011/12/normal-0-false-false-false-en-my-x-none.html. Stored procedure use variable and the size of the variable this will truncate any additional data send to your database. For example if you ask your user about user name and password, if he entered (‘ OR 1 > 0; --) in input text for user name then he will grant login to user system; I hope to read the post about SQL Injection and watch videos.

·         In one of my work I used constant to ensure that I count for 24 month (2 Year), for some reasons we want to change it to 13 month (1 Year and one month), this constant is used in many places in the business logic, after we change the constant in many places we forget others, and there the disaster appear. I sure that if I use it as variable it may help. But my point is if you have variable in your business logic say work hour = 8 hour per day, workdays = 6 day per week if you use this on your application or you client application if your manager e-mailed and said Hi, I want to tell you that our working hours become nine per day and five days per week please change it in your system consider this is a simple example you may solve it by using variables and your admin user change it. If your logic in stored procedure simple you can go to your stored procedure change you parameter from 8 to 9 and from 6 to five, test your procedure then your change has been reflected to all your 100000 users. But if the your constant in your application you must view the code change, test, publish, install the new version, and the bad thing if the constant on your client have fun you want to install your client application on 100000.

·         Stored procedure make separation in your application, you may said there is application layer where your ASP or C# application and there is the business layer where your login in your stored procedure and there is the database layers your tables and views.

I post this topic to share my experience with you, and help you avoid what mistake I did in my life, when you are fresh graduate student and all you remember about how to create system is that you have to create your tables, create your interface, pass values to tables, it is not your problem if the business change. Simply I will did system for you to meet your current business need, it is not my problem if tomorrow you change simple needs work hour from 8 to 9 or so on, I am talking about simple changes that may be fixed in your logic not all the logic change.

Note: if you want to create a database application do not think about current state only, you must take in your consideration what if, do not change the requirement given by customer, try to avoid using constant values, ever thing you do must be easy to change.

I hope this post is helpful.

I really enjoyed when I am writing it, I hope you to add more about stored procedure and database application (share your experience – knowledge – with me).

Written by:  Elmozamil Elamir