Hi all,
I'm new to mysql,Can we create user defined tables in mysql as we do in sql server.
if possible can u provide me an example.
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);
ELSEIF typeoffunction='U' THEN
update MstSchoolInformation set SchoolName=SchoolName,
RegnNo=RegnNo,
RegdBy=RegdBy,
EstablishedOn=EstablishedOn,
address=address,
PrimaryorHighSchool=PrimaryorHighSchool
where SchoolId=SchoolId;
END IF;
end
Thanks in advance