0
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure7`( In p1_no int(11),

In id int(10) ,
 In name  varchar(50) , 
 In surname int(10) ,
 In age int(10) ,
 in gender int(10) )
begin
If ( name1) not null then 
 INSERT INTO krishna.tbl_batperformance (id ,name ,  surname , age , gender )values(
    (SELECT id 
    FROM tbl_std
    WHERE tbl_std.stud_name = name ) ,
    name ,surname, age , gender, fibat1fours );

end if ;
end ;

This is the procedure I had written, but the problem is that I have to check all input parameters in the procedure whether they are null or not and I have to insert them into table.

Mike Lischke
  • 48,925
  • 16
  • 119
  • 181
navya
  • 1
  • if the input parameters is null i have return zero value , if they are not null i have insert – navya Sep 06 '17 at 10:10
  • the query doesn't fully make sense. Why `(SELECT id FROM tbl_std WHERE tbl_std.stud_name = name )`? Then it's not using the ID passed into the procedure. In which case, what is that ID input parameter used for? And also relying matching on a name is a bad idea. I'm sure you've probably met two people with the same name during your lifetime. – ADyson Sep 06 '17 at 10:36
  • And `If ( name1) not null`. You don't seem to have any variable called name1, so it's not clear how this code is supposed to help. It might even crash. That said...you seem to know how to write an `if` statement, so to check all the parameters are not null, in the simplest approach you just need a "valid" variable which is set to true initially, and a series of ifs, one per param. If any param is null, set "valid" to false, then after all the "if" checks have run, check the value of "valid" with a final if statement. if valid is now false, don't run the insert, and return an error instead. – ADyson Sep 06 '17 at 10:37
  • thank q for u r help.....i want a query to check all input parameters in procedure are null or not ....if not null i have to insert them into table . – navya Sep 06 '17 at 10:44
  • yep, that's what I've just described. Try that and if you get stuck with it post the code here and any errors you get. Also worth noting my other comments regarding the reliability of your INSERT query. You also seem to have another input `p1_no` which is not used, so again check what your intention for this param is. If you're not using it, remove it. – ADyson Sep 06 '17 at 10:45
  • 1
    Welcome to Stackoverflow. Please have a read of [How to Ask](https://stackoverflow.com/help/how-to-ask) when you get the chance. Also, make sure to let us know what you've tried and what you got. Show us that code, making sure to also let us know what you expected to see. That said, you should also use [Google](https://google.com), as the first result for `mysql procedure parameter not null` [is this](https://stackoverflow.com/questions/30607172/disallow-null-parameters-to-stored-procedures-in-mysql-mariadb) and another [answer](https://stackoverflow.com/a/330333/1155833).... – rkeet Sep 06 '17 at 11:42

0 Answers0