Currently I am reading a book about MS SQL Stored Procedure. I need to read the book as an anticipation that i will be accepted to an Insurance Company. Just to make sure my brain is not rusty :-P. I believe in-order to understand this guide, you need to know the basic Data Creation and Data Modification commands in T-SQL. I will use this posting to share with you what i *roughly* get from the book 🙂

1. What is Stored Procedure?

Stored procedure is a collection of queries/commands encapsulated in a function stored in the database server. The function may receive parameters which later can be used as the paremeters of the queries/commands. More definition.

2. Why Stored Procedure (SP) is useful?

Because SP will encapsulated your queries/commands into a parameterized queries/data definitions/data modifications. Thus you can standardize the exchange of information between the database server and its users. At the same time you will reduce the debugging-time on Application development since all the business logic are stored in the database. SP also able to help you maintaining security by classifying which users are permitted and which users prohibited to call the Stored Procedures.

3. Why we need Stored Procedure (SP) ?

Because we want to achieve result mentioned on number 2. We can achieve this by replacing the Stored Procedure as a library of functions stored outside the database server. But it will hinder the flexibility of type of Application that using the same business logic. For example, the current business application is using Web-based interface; If we want to create a Desktop-based/Smart Client Application we will be using more time to translate the business logic into different programming language/framework. We also will have major headache if we need to upgrade the Application but not the Business Logic.

4. Why we DON’T need Stored Procedure (SP) ?