Groups > Databases > MySQL for ASP.NET > Re: MySQL insert statement in stored procedure




MySQL insert statement in stored procedure

MySQL insert statement in stored procedure
Tue, 18 Mar 2008 22:11:05 +000
hi,i am new to MySQL and trying to write this simple stored procedure using
INSERT statment,i can't figure what i am doing wrong here.it keeps giving me
following syntex error  

You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near '@intData1 int... What i
am missing here? i am  familiar with SQL server but MySQL is new for me,so if
someone can point me  out in right direction that wud be great.

 DELIMITER $$

DROP PROCEDURE IF EXISTS `txfb`.`InsertSeasonSummaryData` $$
CREATE PROCEDURE `txfb`.`InsertSeasonSummaryData` (@intData1 int,@strData1
varchar(100))
BEGIN
INSERT INTO SEASON_COMMENT
(SEASON_ID,SEASON_COMMENT)values(@intData1,@strData1)
END $$

DELIMITER 

<primary key of the table is set to be auto incrementing,so not included in
above sp)..thanks...
Post Reply
Re: MySQL insert statement in stored procedure
Wed, 26 Mar 2008 13:41:50 +000
I would recomend replacing the "@" prefix with something else, like
"pintData1" instead "@intData1".

Also you should define what kind of parameters you use IN or OUT;

CREATE PROCEDURE `txfb`.`InsertSeasonSummaryData` (IN pintData1 int, IN
pstrData1 varchar(100))
Post Reply
Re: MySQL insert statement in stored procedure
Wed, 26 Mar 2008 14:26:31 +000
thanks Dragan,it worked.
Post Reply
Re: MySQL insert statement in stored procedure
Wed, 26 Mar 2008 20:47:08 +000
here i am more confused again..i am calling this
"fb_spMySqlInsertSeasonCommentData" stored procedure from DAL of my
code and it returns me syntax error in line 1 This is how my query looks when i
open it in MySql administrator->catalogs

CREATE DEFINER=`root`@`localhost` PROCEDURE
`fb_spMySqlInsertSeasonCommentData`(IN data1 int,IN data2 varchar(100))
BEGIN
     insert into season_comment (season_id,season_comment)values(data1,data2) ;
END 

Whats going on ? i am totally lost now...please help

whereas in query browser ,it creates something like this  when i double click
the query

CALL fb_spMySqlInsertSeasonCommentData(1,"test")//i supply the input
data and it executes fine...

query browser shows something like this 

DELIMITER $$

DROP PROCEDURE IF EXISTS `txfb`.`fb_spMySqlInsertSeasonCommentData` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE
`fb_spMySqlInsertSeasonCommentData`(IN data1 int,IN data2 varchar(100))
BEGIN
     insert into season_comment (season_id,season_comment)values(data1,data2) ;
END $$

DELIMITER ;
Post Reply
about | contact