Home » Interview Question » Working with Stored Procedures

Working with Stored Procedures

Working with Stored Procedures

In this tutorial, we learnt that how to create a stored Procedure in SQL and also know how to execute it in MySQL and in PHP

A stored procedure is a set of SQL statements, Stored procedures are compiled once and stored in executable form.Stored procedure can be called by the database engine to connect the programming languages.They are execute on server side and perform a set of actions, before returning the result to the client side.A procedure has a name, a parameter list,Stored procedures are portable, all relational database system supports these stored procedure which is written in SQL.Stored procedure can accepts input and output parameters.Using stored procedure, we can Select,Insert,Update,and Delete data in database.


Types Of Stored  Procedures

User Defined Stored procedure: user defined stored procedure are created by users and stored in the database dictionary.

System Stored Procedure: TO performed many administrative and informational activities we are using system stored procedure.Every time we add or modify a table, make a backup plan, or perform any other administrative function from within Enterprise Manager, we actually call a stored procedure specifically written to complete the desired action. These stored procedures are known as system stored procedures. system stored procedure have name prefixed with sp_. Hence we don't use this prefix when naming user-defined procedures. Here is a list of some useful system defined procedure.
    
Here is a list of some useful system defined stored procedure.
   
sp_rename              used to rename an database object like stored procedure,views and table.
sp_changeowner    used to change the owner of an database object.
sp_help                    provides details on any database object.
sp_helpdb                 Provide the details of the databases defined in the Sql Server.
sp_helptext               rovides the text of a stored procedure reside in Sql Server
sp_depends              provide the details of all database objects that depends on the specific database object.

 

Temporary Stored procedures: The temporary stored procedures have names prefixed with the # symbol. Temporary stored procedures stored in the tempdb databases. These procedures are automatically dropped when the connection  terminates between client and server

Remote Stored Procedures: The remote stored procedures are procedures that are created and stored in databases on remote servers. These remote procedures can be accessed from various servers, provided the users have the appropriate permission

Extended Stored Procedures: Extended procedures provide an interface to external programs for various maintenance activities. These extended procedures starts with the xp_ prefix and stored in Master database. Extended procedures enable creating external routines in a programming language such as C. These procedures are DLLs that an instance of SQL Server can dynamically load and run.

Creating a procedure in mySql

 

Creating a procedure in mySql

 

Call a mySql procedure in php

MySQL IF statement

The MySQL IF statement allows you to execute a set of SQL statement based on a some condition or conditinal expression.An expression can return three value TRUE, FALSE or NULL. MySQL IF Statement

MySQL CASE statement

MySQL also provides an if statement alternative called MySQL CASE conditional statement. it makes the code more readable. MySQL CASE Statement

, , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">