Comments by bgadarsh (Top 4 by date)

thanks it works

This is my table
CREATE TABLE `mstschoolinformation` (
`Schoolid` int(11) NOT NULL,
`SchoolName` varchar(100) CHARACTER SET utf8 NOT NULL,
`RegnNo` varchar(30) CHARACTER SET utf8 NOT NULL,
`Regdby` varchar(100) CHARACTER SET utf8 NOT NULL,
`Establishedon` datetime NOT NULL,
`address` varchar(300) CHARACTER SET utf8 NOT NULL,
`PrimaryorHighSchool` bit(1) DEFAULT NULL,
PRIMARY KEY (`Schoolid`)

this is the contact table where i have multiple contact information
create table ContactDetails
(
Schoolid int,
contactno nvarchar(20))

the stored procedure to insert the inforamtion in the schoolinformation table is the following
CREATE PROCEDURE `MstSchoolInformation_Insert_Update`(
In SchoolId int,
In SchoolName nvarchar(100),
In RegnNo nvarchar(100),
In RegdBy nvarchar(100),
In EstablishedOn datetime,
In address nvarchar(300),
In PrimaryorHighSchool bit,
In typeoffunction char
)
begin
DECLARE MAXID INT;
SELECT ifnull(max(SchoolId),"0")+1 INTO MAXID FROM MSTSCHOOLINFORMATION;
IF typeoffunction='I' THEN
insert into MstSchoolInformation
(SchoolId,
SchoolName,
RegnNo,
RegdBy,
EstablishedOn,
address,
PrimaryorHighSchool) values
(MAXID,
SchoolName,
RegnNo,
RegdBy,
EstablishedOn,
address,
PrimaryorHighSchool);


/* here i will insert the contact info if can be passed as udt like sql*/
ELSEIF typeoffunction='U' THEN
/*SET TRANSACTION ISOLATION LEVEL Repeatable read
start transaction*/

update MstSchoolInformation set SchoolName=SchoolName,
RegnNo=RegnNo,
RegdBy=RegdBy,
EstablishedOn=EstablishedOn,
address=address,
PrimaryorHighSchool=PrimaryorHighSchool
where SchoolId=SchoolId;

/*commit;*/
END IF;
end

thank you Shakil and Sandeep it worked finally