Mysql Stored Procedures

Beginning MySql Stored Procedure

A database stored program—sometimes called a stored module or a stored routine—is
a computer program (a series of instructions associated with a name) that is stored within, and executes within, the database server. The source code and (sometimes) any compiled version of the stored program are almost always held within the database server’s system tables as well. When the program is executed, it is executed within the memory address of a database server process or thread.

There are three major types of MySQL stored programs:
Stored procedures
Stored procedures are the most common type of stored program. A stored procedure is a generic program unit that is executed on request and that can accept multiple input and output parameters.
Stored functions
Stored functions are similar to stored procedures, but their execution results in
the return of a single value. Most importantly, a stored function can be used
within a standard SQL statement, allowing the programmer to effectively extend
the capabilities of the SQL language.
Triggers
Triggers are stored programs that are activated in response to, or are triggered
by, an activity within the database. Typically, a trigger will be invoked in
response to a DML operation (INSERT, UPDATE, DELETE) against a database table.
Triggers can be used for data validation or for the automation of denormalization.

Simple Example of Stored Procedure

1 2 3 4 5 6 7 8 9 10 11 12 13

BEGIN DECLARE l_book_count INTEGER; SELECT COUNT(*) INTO l_book_count FROM books WHERE author LIKE '%XYZ%'; SELECT CONCAT('XYZ has written (or co-written) ', l_book_count , ' books.'); UPDATE books SET author = REPLACE (author, 'XYZ', 'Zeenux') WHERE author LIKE '%XYZ%'; END

Simple Stored Procedure in SQL Server

A stored procedure is a compiled program stored in a database for fast execution. It is ideal for repetitive tasks. In this example i will create a simple stored procedure, for those people who want to quickly start programming in Sql Server.

Lets get our hand Dirty.


Create procedure sp_empByID
@eid INT
AS
BEGIN
SELECT * from emp where emp_id=@eid;
END
GO

Execute this procedure. The procedure will compile and created.

Now execute the procedure by typing this in the Query Window

 sp_empByID 13

This will return the record for the Employee which has an ID of 13