Member Avatar for ramsiva

Any body help me how to use stored procedure and sample code for how to use.

I dont know usage of stored procedure.
Currently i am facing interview questions on stored procedure

  • 3 Contributors
  • 3 Replies
  • 404 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by ramsiva

Dani AI

Generated

A concise, practical primer that complements ’s documentation links and ’s explanation.

Stored procedures are named routines kept inside the database that encapsulate SQL logic; they’re useful for centralizing data rules, reducing client-server round trips for complex operations, and restricting direct table access by granting EXECUTE rather than full table privileges. They also have trade-offs: heavy business logic in the DB can increase CPU load on the database server and make debugging harder. (percona.com)

Common pattern for using them from PHP (mysqli):

  • Create and test the routine directly in the DB client first.
  • Call the routine from PHP using CALL. If the procedure returns one or more SELECT result sets, fetch them with mysqli::multi_query (or prepared statements) and iterate with store_result() / next_result() so the connection stays in sync. OUT/INOUT parameters are typically read via session variables (e.g., SET @p = ...; CALL sp(@p); SELECT @p;). Example pattern:
<?php
$mysqli = new mysqli('host','user','pass','db');
$mysqli->real_escape_string($val);
if ($mysqli->multi_query("CALL sp_name('{$val}')")) {
  do {
    if ($res = $mysqli->store_result()) {
      while ($row = $res->fetch_assoc()) { /* handle row */ }
      $res->free();
    }
  } while ($mysqli->next_result());
}
$out = $mysqli->query("SELECT @out_param AS val")->fetch_assoc()['val'];

The mysqli stored-procedures and multi-query behavior is documented in the PHP manual. (php.net)

PDO note: PDO supports binding for input, output, and input/output parameters (PDO::PARAM_INPUT_OUTPUT) but driver behavior varies; test on the target PHP + MySQL combination. Prepared statements remain the safer route for parameter escaping. (php.net)

Troubleshooting / interview points to mention: “Commands out of sync” happens when not consuming all result sets; GRANT EXECUTE controls routine execution rights; stored routines can hurt scalability if they do heavy CPU work—keep them small and test performance. These are common practical issues to show familiarity with during interviews. (php.net)

Recommended Answers

All 3 Replies

Member Avatar for tobyITguy

Yet another Link

Stored procedures are used to store some actions on your database. So that those actions can be peformed by people without having direct access to the data.

It allows for variable declaration. Its like functions in programming.

DELIMITER //

CREATE PROCEDURE `multiply` (OUT var1 INT)
BEGIN
    SET var1 = var1 * 2;
END //

When the mutiply procedure is called like so

CALL multiply(5)

its given a parameter(in this case 5).

That parameter is then multiplied by two and returned.

Within the "begin" and "end", you can put any type of query.

It limits what users can do on your database and enhances security and ease of use.

The "DELIMITER" tells sql your done with typing a statement.

In the example we used // as our delimiter.

Hope that helps.

Member Avatar for ramsiva

Very Thanx,

usefull information

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.