Insert query in mysql using stored procedure

Question

I have to insert datas using stored procedure in mysql.Here is my SP and function call to that SP.But I can't insert values using them. Can you please verify and give me what to do? SP is:

delimiter ; ;

DROP PROCEDURE IF EXISTS sp_insertUserDetails ; ;
CREATE PROCEDURE sp_insertUserDetails( ) 
BEGIN 
      INSERT INTO tbl_userDetails
       ( strEmail,bitAllowClicktoFBProfile,bitIsAbbreviateLastName, strAboutMe )
VALUES ($strEmail, $bitAllow, $bitAbbreviate, $strAbout);
END

Call to this SP from php file:

$query = mysql_query("CALL sp_insertUserDetails($strEmail, $bitAllow, $bitAbbreviate, $strAbout)");

change delimiter value and try

xkeshav
– xkeshav

2011-11-16 05:08:02 +00:00
Commented Nov 16, 2011 at 5:08 — xkeshav
– xkeshav, Commented Nov 16, 2011 at 5:08

Adrian Cornish · Accepted Answer · 2011-11-16 05:14:08Z

1

You dont define any arguments to your SP - did you even try call it straight from SQL eg:

mysql> call sp_insertUserDetails("foo", "bar", "dave", "str");
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE test.sp_insertUserDetails; expected 0, got 4

You need something like this

DROP PROCEDURE IF EXISTS sp_name;
delimiter ;;
CREATE PROCEDURE sp_name(fname varchar (20), lname varchar(20))
BEGIN
   SELECT concat('Hello ', fname, ',', lname);
END
;;
delimiter ;

answered Nov 16, 2011 at 5:14

Adrian Cornish

24k13 gold badges64 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

xkeshav · Accepted Answer · 2011-11-16 05:20:07Z

0

just do this

HERE i am assuming that all values are in varchar

DELIMITER //

DROP PROCEDURE IF EXISTS sp_insertUserDetails; 
CREATE PROCEDURE sp_insertUserDetails( 
 IN strEmail varchar(255), 
 IN bitAllow varchar(255),
 IN bitAbbreviate varchar(255),
 IN strAbout varchar(255)
) 
BEGIN 
      INSERT INTO tbl_userDetails
       ( strEmail,bitAllowClicktoFBProfile,bitIsAbbreviateLastName, strAboutMe )
VALUES (strEmail, bitAllow, bitAbbreviate, strAbout);
END //
DELIMITER ;

and call like this

mysql_query( CALL sp_insertUserDetails($strEmail, $bitAllow, $bitAbbreviate, $strAbout) )

answered Nov 16, 2011 at 5:20

xkeshav

54.2k47 gold badges183 silver badges255 bronze badges

1 Comment

Karthika Over a year ago

Sorry diEcho, Now it is error free. But insertion into the table is not working.

Collectives™ on Stack Overflow

Insert query in mysql using stored procedure

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related