Mixing
SQL procedure - Multiple value
up vote
0
down vote
favorite
I have this procedure :
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE `id_var` varchar(255);
DECLARE `cur1` CURSOR FOR
SELECT `id` FROM `clients`
WHERE `status` = 'Active';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TABLE IF EXISTS `tblquota_nc`;
CREATE TABLE IF NOT EXISTS `tblquota_nc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`email` varchar(255),
`pack_id` int(11) NOT NULL,
`pack_name` varchar(255) NOT NULL,
`quota` int(11) NULL,
PRIMARY KEY (`id`)
);
OPEN cur1;
read_loop: LOOP
FETCH NEXT
FROM cur1
INTO id_var;
IF done THEN
LEAVE read_loop;
END IF;
SELECT clients.id as `User ID`, `email`, `packageid` as `Pack ID`, `name` as `Pack`, (CASE
WHEN `name` = "Basic" THEN '10'
WHEN `name` = "Silver" THEN '100'
WHEN `name` = "Gold" THEN '1000'
ELSE '10'
END) as Quota INTO @mid, @mail, @p_id, @packname, @quota
FROM `clients`
INNER JOIN `tblhosting` ON clients.id = tblhosting.userid
INNER JOIN `tblproducts` ON tblhosting.packageid = tblproducts.id
WHERE clients.status = 'Active'
AND tblhosting.domainstatus = 'Active'
AND clients.id = id_var;
IF (SELECT id FROM tblquota_nc WHERE user_id = @mid) THEN
BEGIN
END;
ELSE
BEGIN
if (@mid IS NOT NULL AND @p_id IS NOT NULL AND @packname IS NOT NULL) then
INSERT INTO tblquota_nc (user_id, email, pack_id, pack_name, quota) VALUES (@mid, @mail, @p_id, @packname, @quota);
end if;
END;
END IF;
END LOOP;
CLOSE cur1;
END
It seems like I have an error because the SELECT statment return several values. I thought doing another loop with these results to make an insert into the new table. I want to make a new table from these information.
table clients:
id | email | status
----------------------------
1 | user1@mail.com | Active
2 | user2@mail.com | Inactive
3 | user3@mail.com | Active
table tblhosting
id | userid | packageid | domainstatus
------------------------------------------------
1 | 1 | 2 | Active
2 | 2 | 3 | Active
3 | 3 | 1 | Active
table tblproducts
id | name
-----------
1 | Basic
2 | Silver
3 | Gold
I expect result like :
id | user_id | email | pack_id | pack_name | quota
-----------------------------------------------------------
1 | 1 | user1@mail.com | 2 | Silver | 100
2 | 2 | user2@mail.com | 3 | Gold | 1000
3 | 3 | user3@mail.com | 1 | Basic | 10
If I put max in the case statment, it will work but will not show all data.
mysql sql stored-procedures
asked 20 mins ago
executable
815221
add a comment |
up vote
0
down vote
favorite
I have this procedure :
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE `id_var` varchar(255);
DECLARE `cur1` CURSOR FOR
SELECT `id` FROM `clients`
WHERE `status` = 'Active';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TABLE IF EXISTS `tblquota_nc`;
CREATE TABLE IF NOT EXISTS `tblquota_nc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`email` varchar(255),
`pack_id` int(11) NOT NULL,
`pack_name` varchar(255) NOT NULL,
`quota` int(11) NULL,
PRIMARY KEY (`id`)
);
OPEN cur1;
read_loop: LOOP
FETCH NEXT
FROM cur1
INTO id_var;
IF done THEN
LEAVE read_loop;
END IF;
SELECT clients.id as `User ID`, `email`, `packageid` as `Pack ID`, `name` as `Pack`, (CASE
WHEN `name` = "Basic" THEN '10'
WHEN `name` = "Silver" THEN '100'
WHEN `name` = "Gold" THEN '1000'
ELSE '10'
END) as Quota INTO @mid, @mail, @p_id, @packname, @quota
FROM `clients`
INNER JOIN `tblhosting` ON clients.id = tblhosting.userid
INNER JOIN `tblproducts` ON tblhosting.packageid = tblproducts.id
WHERE clients.status = 'Active'
AND tblhosting.domainstatus = 'Active'
AND clients.id = id_var;
IF (SELECT id FROM tblquota_nc WHERE user_id = @mid) THEN
BEGIN
END;
ELSE
BEGIN
if (@mid IS NOT NULL AND @p_id IS NOT NULL AND @packname IS NOT NULL) then
INSERT INTO tblquota_nc (user_id, email, pack_id, pack_name, quota) VALUES (@mid, @mail, @p_id, @packname, @quota);
end if;
END;
END IF;
END LOOP;
CLOSE cur1;
END
It seems like I have an error because the SELECT statment return several values. I thought doing another loop with these results to make an insert into the new table. I want to make a new table from these information.
table clients:
id | email | status
----------------------------
1 | user1@mail.com | Active
2 | user2@mail.com | Inactive
3 | user3@mail.com | Active
table tblhosting
id | userid | packageid | domainstatus
------------------------------------------------
1 | 1 | 2 | Active
2 | 2 | 3 | Active
3 | 3 | 1 | Active
table tblproducts
id | name
-----------
1 | Basic
2 | Silver
3 | Gold
I expect result like :
id | user_id | email | pack_id | pack_name | quota
-----------------------------------------------------------
1 | 1 | user1@mail.com | 2 | Silver | 100
2 | 2 | user2@mail.com | 3 | Gold | 1000
3 | 3 | user3@mail.com | 1 | Basic | 10
If I put max in the case statment, it will work but will not show all data.
mysql sql stored-procedures
asked 20 mins ago
executable
815221
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have this procedure :
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE `id_var` varchar(255);
DECLARE `cur1` CURSOR FOR
SELECT `id` FROM `clients`
WHERE `status` = 'Active';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TABLE IF EXISTS `tblquota_nc`;
CREATE TABLE IF NOT EXISTS `tblquota_nc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`email` varchar(255),
`pack_id` int(11) NOT NULL,
`pack_name` varchar(255) NOT NULL,
`quota` int(11) NULL,
PRIMARY KEY (`id`)
);
OPEN cur1;
read_loop: LOOP
FETCH NEXT
FROM cur1
INTO id_var;
IF done THEN
LEAVE read_loop;
END IF;
SELECT clients.id as `User ID`, `email`, `packageid` as `Pack ID`, `name` as `Pack`, (CASE
WHEN `name` = "Basic" THEN '10'
WHEN `name` = "Silver" THEN '100'
WHEN `name` = "Gold" THEN '1000'
ELSE '10'
END) as Quota INTO @mid, @mail, @p_id, @packname, @quota
FROM `clients`
INNER JOIN `tblhosting` ON clients.id = tblhosting.userid
INNER JOIN `tblproducts` ON tblhosting.packageid = tblproducts.id
WHERE clients.status = 'Active'
AND tblhosting.domainstatus = 'Active'
AND clients.id = id_var;
IF (SELECT id FROM tblquota_nc WHERE user_id = @mid) THEN
BEGIN
END;
ELSE
BEGIN
if (@mid IS NOT NULL AND @p_id IS NOT NULL AND @packname IS NOT NULL) then
INSERT INTO tblquota_nc (user_id, email, pack_id, pack_name, quota) VALUES (@mid, @mail, @p_id, @packname, @quota);
end if;
END;
END IF;
END LOOP;
CLOSE cur1;
END
It seems like I have an error because the SELECT statment return several values. I thought doing another loop with these results to make an insert into the new table. I want to make a new table from these information.
table clients:
id | email | status
----------------------------
1 | user1@mail.com | Active
2 | user2@mail.com | Inactive
3 | user3@mail.com | Active
table tblhosting
id | userid | packageid | domainstatus
------------------------------------------------
1 | 1 | 2 | Active
2 | 2 | 3 | Active
3 | 3 | 1 | Active
table tblproducts
id | name
-----------
1 | Basic
2 | Silver
3 | Gold
I expect result like :
id | user_id | email | pack_id | pack_name | quota
-----------------------------------------------------------
1 | 1 | user1@mail.com | 2 | Silver | 100
2 | 2 | user2@mail.com | 3 | Gold | 1000
3 | 3 | user3@mail.com | 1 | Basic | 10
If I put max in the case statment, it will work but will not show all data.
mysql sql stored-procedures
asked 20 mins ago
executable
815221
I have this procedure :
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE `id_var` varchar(255);
DECLARE `cur1` CURSOR FOR
SELECT `id` FROM `clients`
WHERE `status` = 'Active';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TABLE IF EXISTS `tblquota_nc`;
CREATE TABLE IF NOT EXISTS `tblquota_nc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`email` varchar(255),
`pack_id` int(11) NOT NULL,
`pack_name` varchar(255) NOT NULL,
`quota` int(11) NULL,
PRIMARY KEY (`id`)
);
OPEN cur1;
read_loop: LOOP
FETCH NEXT
FROM cur1
INTO id_var;
IF done THEN
LEAVE read_loop;
END IF;
SELECT clients.id as `User ID`, `email`, `packageid` as `Pack ID`, `name` as `Pack`, (CASE
WHEN `name` = "Basic" THEN '10'
WHEN `name` = "Silver" THEN '100'
WHEN `name` = "Gold" THEN '1000'
ELSE '10'
END) as Quota INTO @mid, @mail, @p_id, @packname, @quota
FROM `clients`
INNER JOIN `tblhosting` ON clients.id = tblhosting.userid
INNER JOIN `tblproducts` ON tblhosting.packageid = tblproducts.id
WHERE clients.status = 'Active'
AND tblhosting.domainstatus = 'Active'
AND clients.id = id_var;
IF (SELECT id FROM tblquota_nc WHERE user_id = @mid) THEN
BEGIN
END;
ELSE
BEGIN
if (@mid IS NOT NULL AND @p_id IS NOT NULL AND @packname IS NOT NULL) then
INSERT INTO tblquota_nc (user_id, email, pack_id, pack_name, quota) VALUES (@mid, @mail, @p_id, @packname, @quota);
end if;
END;
END IF;
END LOOP;
CLOSE cur1;
END
It seems like I have an error because the SELECT statment return several values. I thought doing another loop with these results to make an insert into the new table. I want to make a new table from these information.
table clients:
id | email | status
----------------------------
1 | user1@mail.com | Active
2 | user2@mail.com | Inactive
3 | user3@mail.com | Active
table tblhosting
id | userid | packageid | domainstatus
------------------------------------------------
1 | 1 | 2 | Active
2 | 2 | 3 | Active
3 | 3 | 1 | Active
table tblproducts
id | name
-----------
1 | Basic
2 | Silver
3 | Gold
I expect result like :
id | user_id | email | pack_id | pack_name | quota
-----------------------------------------------------------
1 | 1 | user1@mail.com | 2 | Silver | 100
2 | 2 | user2@mail.com | 3 | Gold | 1000
3 | 3 | user3@mail.com | 1 | Basic | 10
If I put max in the case statment, it will work but will not show all data.
mysql sql stored-procedures
mysql sql stored-procedures
asked 20 mins ago
executable
815221
asked 20 mins ago
executable
815221
asked 20 mins ago
executable
815221
asked 20 mins ago
executable
815221
asked 20 mins ago
executable
815221
815221
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
I don't think you need a stored procedure to do this. Just use CREATE TABLE ... SELECT syntax:
CREATE TABLE tblquota_nc (id INT AUTO_INCREMENT PRIMARY KEY) AS
SELECT c.id as user_id
, c.`email`
, h.`packageid` as pack_id
, p.`name` as pack_name
, (CASE WHEN `name` = "Basic" THEN '10'
WHEN `name` = "Silver" THEN '100'
WHEN `name` = "Gold" THEN '1000'
ELSE '10'
END) as quota
FROM `clients` c
LEFT JOIN `tblhosting` h ON c.id = h.userid
INNER JOIN `tblproducts` p ON h.packageid = p.id
ORDER BY c.id;
Output from SELECT * FROM tblquota_nc:
id user_id email pack_id pack_name quota
1 1 user1@mail.com 2 Silver 100
2 2 user2@mail.com 3 Gold 1000
3 3 user3@mail.com 1 Basic 10
Demo on dbfiddle
answered 8 mins ago
Nick
18.7k41433
It works really well and much faster than my procedure !
– executable
5 mins ago
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
I don't think you need a stored procedure to do this. Just use CREATE TABLE ... SELECT syntax:
CREATE TABLE tblquota_nc (id INT AUTO_INCREMENT PRIMARY KEY) AS
SELECT c.id as user_id
, c.`email`
, h.`packageid` as pack_id
, p.`name` as pack_name
, (CASE WHEN `name` = "Basic" THEN '10'
WHEN `name` = "Silver" THEN '100'
WHEN `name` = "Gold" THEN '1000'
ELSE '10'
END) as quota
FROM `clients` c
LEFT JOIN `tblhosting` h ON c.id = h.userid
INNER JOIN `tblproducts` p ON h.packageid = p.id
ORDER BY c.id;
Output from SELECT * FROM tblquota_nc:
id user_id email pack_id pack_name quota
1 1 user1@mail.com 2 Silver 100
2 2 user2@mail.com 3 Gold 1000
3 3 user3@mail.com 1 Basic 10
Demo on dbfiddle
answered 8 mins ago
Nick
18.7k41433
It works really well and much faster than my procedure !
– executable
5 mins ago
add a comment |
up vote
1
down vote
accepted
I don't think you need a stored procedure to do this. Just use CREATE TABLE ... SELECT syntax:
CREATE TABLE tblquota_nc (id INT AUTO_INCREMENT PRIMARY KEY) AS
SELECT c.id as user_id
, c.`email`
, h.`packageid` as pack_id
, p.`name` as pack_name
, (CASE WHEN `name` = "Basic" THEN '10'
WHEN `name` = "Silver" THEN '100'
WHEN `name` = "Gold" THEN '1000'
ELSE '10'
END) as quota
FROM `clients` c
LEFT JOIN `tblhosting` h ON c.id = h.userid
INNER JOIN `tblproducts` p ON h.packageid = p.id
ORDER BY c.id;
Output from SELECT * FROM tblquota_nc:
id user_id email pack_id pack_name quota
1 1 user1@mail.com 2 Silver 100
2 2 user2@mail.com 3 Gold 1000
3 3 user3@mail.com 1 Basic 10
Demo on dbfiddle
answered 8 mins ago
Nick
18.7k41433
It works really well and much faster than my procedure !
– executable
5 mins ago
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
I don't think you need a stored procedure to do this. Just use CREATE TABLE ... SELECT syntax:
CREATE TABLE tblquota_nc (id INT AUTO_INCREMENT PRIMARY KEY) AS
SELECT c.id as user_id
, c.`email`
, h.`packageid` as pack_id
, p.`name` as pack_name
, (CASE WHEN `name` = "Basic" THEN '10'
WHEN `name` = "Silver" THEN '100'
WHEN `name` = "Gold" THEN '1000'
ELSE '10'
END) as quota
FROM `clients` c
LEFT JOIN `tblhosting` h ON c.id = h.userid
INNER JOIN `tblproducts` p ON h.packageid = p.id
ORDER BY c.id;
Output from SELECT * FROM tblquota_nc:
id user_id email pack_id pack_name quota
1 1 user1@mail.com 2 Silver 100
2 2 user2@mail.com 3 Gold 1000
3 3 user3@mail.com 1 Basic 10
Demo on dbfiddle
answered 8 mins ago
Nick
18.7k41433
I don't think you need a stored procedure to do this. Just use CREATE TABLE ... SELECT syntax:
CREATE TABLE tblquota_nc (id INT AUTO_INCREMENT PRIMARY KEY) AS
SELECT c.id as user_id
, c.`email`
, h.`packageid` as pack_id
, p.`name` as pack_name
, (CASE WHEN `name` = "Basic" THEN '10'
WHEN `name` = "Silver" THEN '100'
WHEN `name` = "Gold" THEN '1000'
ELSE '10'
END) as quota
FROM `clients` c
LEFT JOIN `tblhosting` h ON c.id = h.userid
INNER JOIN `tblproducts` p ON h.packageid = p.id
ORDER BY c.id;
Output from SELECT * FROM tblquota_nc:
id user_id email pack_id pack_name quota
1 1 user1@mail.com 2 Silver 100
2 2 user2@mail.com 3 Gold 1000
3 3 user3@mail.com 1 Basic 10
Demo on dbfiddle
answered 8 mins ago
Nick
18.7k41433
answered 8 mins ago
Nick
18.7k41433
answered 8 mins ago
Nick
18.7k41433
answered 8 mins ago
Nick
18.7k41433
18.7k41433
It works really well and much faster than my procedure !
– executable
5 mins ago
add a comment |
It works really well and much faster than my procedure !
– executable
5 mins ago
It works really well and much faster than my procedure !
– executable
5 mins ago
It works really well and much faster than my procedure !
– executable
5 mins ago
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53371595%2fsql-procedure-multiple-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
