Show
This post will cover how to create Stored Procedures using PHP My Admin. Next part will cover how to execute Stored Procedures using Laravel.First of all what is Stored Procedure? A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. You can pass parameters to the stored procedure to get data based on Dynamic values. Step -1 : Open PHP My Admin and select the database to create stored procedure Step -2 : Go to Routines menu & Click on Add routine. Step -3 : By Clicking on Add Routine Link, PHPMyAdmin will open Pop-up. Step -4 : Follow the steps and create stored procedure.Create stored procedure to get data from users table( Without Parameters) . Stored procedure Without ParametersWithout Parameters
Stored Procedure with ParametersWith Parameters
Execute Stored Procedure from PHP MyAdminPHP MyAdmin will display list of created Stored Procedures. List of Stored Procedure Click on Execute link to Run Specific Stored Procedure. Procedure without parameters will directly Run Query and list out the data Procedure parameters will open Pop up to add parameters, then Run Procedure and get result data. That’s it. Hope this article helps you guys to understand how to mange Stored Procedure Using PHP My Admin. In the Next article I will explain how to Execute Stored Procedure In Laravel Framework. Part 2 : https://medium.com/@nishitvmaheta/how-to-execute-stored-procedure-using-laravel-8d067c306154 Stored procedures provide PHP programmers with pre-programmed database applications that retrieve and edit MySQL records. Stored procedures let you send parameters to the database program, so you can retrieve only a specific set of records. You must connect to the database and use the "mysql_query" function to call a MySQL stored procedure from a PHP page.
SQL Stored Procedures for SQL ServerWhat is a Stored Procedure?A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed. Stored Procedure Syntax CREATE PROCEDURE procedure_name Execute a Stored ProcedureDemo DatabaseBelow is a selection from the "Customers" table in the Northwind sample database:
Stored Procedure ExampleThe following SQL statement creates a stored procedure named "SelectAllCustomers" that selects all records from the "Customers" table: Example CREATE PROCEDURE SelectAllCustomers Execute the stored procedure above as follows: ExampleEXEC SelectAllCustomers; Stored Procedure With One ParameterThe following SQL statement creates a stored procedure that selects Customers from a particular City from the "Customers" table: Example CREATE PROCEDURE SelectAllCustomers @City nvarchar(30) Execute the stored procedure above as follows: ExampleEXEC SelectAllCustomers @City = 'London'; Stored Procedure With Multiple ParametersSetting up multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below. The following SQL statement creates a stored procedure that selects Customers from a particular City with a particular PostalCode from the "Customers" table: Example CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10) Execute the stored procedure above as follows: ExampleEXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP'; Why we use stored procedure?A stored procedure provides an important layer of security between the user interface and the database. It supports security through data access controls because end users may enter or change data, but do not write procedures.
What is stored procedure in PHP?A stored procedure is a subroutine stored in the database catalog. Applications can call and execute the stored procedure. The CALL SQL statement is used to execute a stored procedure. Stored procedures can have IN , INOUT and OUT parameters, depending on the MySQL version.
Why we use stored procedure instead of query?A stored procedure is invoked as a function call instead of a SQL query. Stored procedures can have parameters for both passing values into the procedure and returning values from the call. Results can be returned as a result set, or as an OUT parameter cursor.
|