how to check variables declared inside procedures?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

how to check variables declared inside procedures?

Post by raghavan20 »

1. I have a procedure and I want to check the values stored in the variables declared in the procedure. Can you tell me is there is anyway to echo the variables declared whereever and whenever one likes....

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
Post Reply