2. When I execute the procedure, it always returns rows affected as zero...I do not know why it says that even if I just have one select statement in the procedure and commenting out all the rest of code in it.
Code: Select all
CREATE PROCEDURE `OnlineSystem`.`SavedItemsToShoppingCartLines`(m_userId int)
deterministic
begin
declare m_productId varchar(30); /* Holds the product ID */
declare m_productIdType int; /* Holds the product id type from catalogue listing table */
declare m_quantity int; /* Holds the quantity available from the listing table */
declare m_listingId int; /* Get the row id for the product ID in the listing table */
declare m_doesProductExistInSC int default NULL; /* Check whether a product exist in the ShoppingCartProduct table */
declare m_addressBookId int; /* Get the first address of the user */
select `productId`, `productIdType` into `m_productId`, `m_productIdType` from `SavedItemsList`
where `userId` = m_userId;
select `id`, `quantity` into m_listingId, m_quantity from `catalogue`.`listing`
where `productId` = m_productId;
select `id` into m_doesProductExistInSC from `OnlineSystem`.`ShoppingCartProduct` where productId = m_productId;
if ( m_quantity <> 0 and m_doesProductExistInSC is NULL ) then
/*insert into `OnlineSystem`.`ShoppingCartProduct`
(`id`, `productTypeId`, `productIdType`, `DeliveryOptionId`, `AddressBookId`, `userId`, `productId`, `quantity`, `addInTime`)
values
(NULL, 1, m_productIdType, NULL, NULL, m_userId, m_productId, 1, NOW());*/
end if;
end
mysql> call SavedItemsToShoppingCartLines(55);
Query OK, 0 rows affected