CODEWITHSUNDEEP
phpmysqlstored-procedures
Karthika
Karthika

Reputation: 47

Insert query in mysql using stored procedure

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)");

Upvotes: 0

Views: 11974

Answers (2)

xkeshav
xkeshav

Reputation: 54016

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) )

Upvotes: 0

Adrian Cornish
Adrian Cornish

Reputation: 23858

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 ;

Upvotes: 1

Related Questions