Stored procedures...

Hi……thinking of stored procedures!!!!!!!!!! Let me explain briefly…. 

   Stored procedure is a series of SQL statements stored in the DATABASE SERVER to accomplish a particular task.. Those SQL statements are just like commands such as conditional statements, looping statements, side effects including functions identified from set of parameters, return type etc. Most of the time stored procedures are pre compiled for efficiency enhancements. Although these things are more familiar with dynamic SQL, you can use general purpose languages like C , C# , Java…

Advantages                                          

• High performance                                                                        In normal databases , Data base client connects with the DBMS creating high network traffic, but applications using stored procedures are used to connect with the stored procedure rather than DBMS upgrading the performance.In addition to this some data processing is done in the DBMS..So only application server and database server are involved in sending commands through network..On the other hand pre - compilation feature contribute for significant performance enhancement.Although when it is execute for the first time it requires compilation. Then after for subsequent users no need to go for compilation.By the way,,, The compilation doesn't depend on the language....                                                                                                                                                                                                             
• Separation  of concerns for application developers      When we are calling a stored procedure it requires things such as name , parameters of the application programe to run the procedure, but there must be a separation between application programme and the data ..at the same time there must be a separation of SQL structure from DBMS.         

• Testability, Stability and maintainability                    The main reason of going for stored procedure is having use of those procedures whenever we need it.When there are st of procedures it is easy to maintain. As those are consist of well defined set of methods we can call them and test them even check and test them with others..

• Security                                                                    We can implement security issues easily by applying authorization restrictions based on the authority of the user. We can utilized the full power of the Data Base Server.

  

          Disadvantages

• Programming environment not as advanced as modern, visual IDE s.                                                                                            There is a somewhat difficulty in adopting to this environment...There are fewer advance writing , testing , debugging features are available in SQL server , Oracle etc. though many features are supported  at visual studio, netbeans environment..... 

• Can be used to restrict application and user access to data

• Extra layer of hidden complexity on top of SQL statements                                                                      

          Examples                                              

                        Creating Stored procedures

     CREATE PROCEDURE GetUndergradsInFaculty

     @Faculty int

     AS

     SELECT *

       FROM Undergrads

     WHERE FacID=@Faculty

        

        Modifying Stored procedures

      [schema.]procedure_name COMPILE[DEBUG]

      [REUSE SETTINGS];

           Droping Stored procedures

    DROP PROCEDURE GetUndergradsInFaculty; 

                                                                                          

          Calling Stored procedures

    EXEC SPnAME(parameter_value_list);

                        

Console window

From another  Stored procedure