Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

i am write this stored procedure to delete one row

       DELIMITER $$

      CREATE DEFINER=`root`@`localhost` PROCEDURE `spr_StdDelete`(IN ID INT)
      BEGIN


      DELETE FROM students WHERE ID=id;


      END

but when i call it from php its delete all table

     $result = mysql_query("Call spr_StdDelete('$ID')")
     or die(mysql_error());

then i try this sp but its not working

      DELIMITER $$
      CREATE DEFINER=`root`@`localhost` PROCEDURE `spr_StdDelete`(IN ID INT)
      BEGIN
      DELETE FROM students WHERE ID=@ID;
      END

plz help ...

while when i use this query its delete the selected row

      $result = mysql_query("DELETE FROM students WHERE ID=$ID")
      or die(mysql_error());
share|improve this question

closed as off-topic by Paul White, Mark Storey-Smith, Max Vernon, Michael - sqlbot, Kin Feb 20 at 14:34

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, Mark Storey-Smith, Max Vernon, Michael - sqlbot, Kin
If this question can be reworded to fit the rules in the help center, please edit the question.

    
Just so you know, your DELETE FROM students WHERE ID=id; query is the same as writing delete * from students; –  Phil Feb 5 at 8:03
    
yes i am change it to be DELETE FROM students WHERE ID=@id ; but still not work –  user2997218 Feb 5 at 8:08
1  
@Phil, you're right, although in this case, the server resolves the ambiguity by assuming you are comparing the local variable to itself, since local variables are case insensitive and have precedence over column names when there's more than one possible interpretation. Same net result, if the variable isn't null. –  Michael - sqlbot Feb 6 at 2:22

1 Answer 1

up vote 0 down vote accepted
  CREATE DEFINER=`root`@`localhost` PROCEDURE `spr_StdDelete`(IN STUDENT_ID INT)
  BEGIN

  DELETE FROM students WHERE id=STUDENT_ID;

  END
share|improve this answer
    
thnx its working done –  user2997218 Feb 5 at 8:10
1  
“A local variable should not have the same name as a table column. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable.” — dev.mysql.com/doc/refman/5.6/en/local-variable-scope.html –  Michael - sqlbot Feb 6 at 2:26

Not the answer you're looking for? Browse other questions tagged or ask your own question.