WRXKCpwC

1DELIMITER //
2
3DROP PROCEDURE IF EXISTS insert_user_group_info
4//
5CREATE PROCEDURE insert_group_user_info(groupname varchar(50), groupdesc varchar(100), 
6createddate varchar(50), createdby varchar(100))
7BEGIN
8   DECLARE RETURN_VAL INT UNSIGNED DEFAULT 0;
9   DECLARE NEWGROUPID INT UNSIGNED DEFAULT 0;
10START TRANSACTION;
11
12   Insert into group_tbl (`groupname`,
13   `groupdesc`,
14   `groupusers`,
15   `createdon`,
16   `createdby`,
17   `groupstatus`) 
18    values
19   (groupname,
20    groupdesc,
21    '1',
22    createddate,
23    createdby,
24    '1');
25
26    SET NEWGROUPID = LAST_INSERT_ID();
27
28   INSERT INTO useringroup_tbl
29   ( groupid,
30     username,
31     regdate,
32     joindate,
33     userstatus,
34     roleid)
35   VALUES
36   ( NEWGROUPID,
37     createdby,
38     createddate,
39     createddate,
40     '1',
41     '1');
42
43     INSERT INTO userinrole_tbl
44   ( username,
45     groupid,
46     roleid)
47   VALUES
48   (createdby, 
49    NEWGROUPID,
50    '1');
51
52   SET RETURN_VAL = LAST_INSERT_ID();
53
54   SELECT RETURN_VAL;
55
56COMMIT;
57END//
58DELIMITER ;
59	
60CREATE TABLE `group_tbl` 
61(
62  `groupid` int(11) NOT NULL auto_increment,
63  `groupname` varchar(50) NOT NULL,
64  `groupdesc` varchar(100) NOT NULL,
65  `groupusers` int(11) NOT NULL,
66  `createdon` datetime NOT NULL,
67  `createdby` varchar(50) NOT NULL,
68  `groupstatus` tinyint(4) NOT NULL,
69  PRIMARY KEY  (`groupid`)
70);
71	
72CREATE TABLE IF NOT EXISTS `useringroup_tbl` 
73(
74  `groupid` int(11) NOT NULL,
75  `username` varchar(50) NOT NULL,
76  `regdate` datetime NOT NULL,
77  `joindate` datetime NOT NULL default '0000-00-00 00:00:00',
78  `userstatus` tinyint(4) NOT NULL,
79  `closingdate` datetime NOT NULL default '0000-00-00 00:00:00',
80  `roleid` int(11) NOT NULL default '0',
81  PRIMARY KEY  (`groupid`,`username`)
82);
83	
84CREATE TABLE IF NOT EXISTS `userinrole_tbl` 
85(
86  `id` int(11) NOT NULL auto_increment,
87  `username` varchar(50) NOT NULL,
88  `groupid` int(11) NOT NULL,
89  `roleid` int(11) NOT NULL,
90  PRIMARY KEY  (`id`)
91);
92	
93call insert_group_user_info('test name','test description','2015-05-10 12:10:12','XYZuser')