stored procedure problem

[mskat02]

I am trying to insert a set of attribute rows into a table based on the range of a unique key (product_id) and three passed variables. This is on a MySQL database. The three variables are the start_product_id, the end_product_id and the batch_attr_id.

The stored procedure does not return an error, but it does not product any inserts.

code start here ---------------

insert into `product_attribute` (`product_id`, `attribute_id`, `language_id`, `text`)
(
SELECT a.`product_id` 
        ,b.`attr_id`
        ,b.`lang_id`
        ,b.`attr_value`

FROM     `product` a 
        ,`batch_attr_detail` b
        ,`batch_attr_set` c

WHERE c.`batch_attr_id` = @`batch_attr_id`
  and c.`batch_attr_id` = b.`batch_attr_id`
  and a.`product_id` between @`start_product_id` and @`end_product_id`

    );
 --------------end of code   


------------------------
I am calling the stored procude with this syntax - 



 call batch_update_proc(575,576,112);



There are records with both 575 and 576 as product IDs and the batch set 112 exists. If I take the select out of the procedure and substitute scalars, it works fine. Just no insert.

Can any one please tell me what stupid error I am making? I have spent hours on this!

Thx - Kat

[ryantroop]

It would really help to see the whole declaration, so we can see the incoming variables.

At first glance, it looks like you're doing something funny with your vars by using quotes...

@'foo' should be @foo, And in MySQL, I have found more success just avoiding user declared variables and just using the VARs passed in.

Of course, your mileage may vary...

[mskat02]

Yes, I agree on the declaration. I did not have those in the original, but someone suggested that I explicitly declare the variables. Also on the blasted quotes - I am not sure how or why, but my installation requires the different kind of quotes. If you know how to turn them off, I would be eternally grateful. They are a total PAIN.

Here is an image of the stored procedure as it sit. Notice that I do not have the explicit declaration in this one.
Click Here

Thanks - Kat

[ryantroop]

may sound silly, but can you try without the () surrounding your select?

insert into `product_attribute` (`product_id`, `attribute_id`, `language_id`, `text`)
SELECT a.`product_id` 
        ,b.`attr_id`
        ,b.`lang_id`
        ,b.`attr_value`

FROM     `product` a 
        ,`batch_attr_detail` b
        ,`batch_attr_set` c

WHERE c.`batch_attr_id` = @`batch_attr_id`
  and c.`batch_attr_id` = b.`batch_attr_id`
  and a.`product_id` between @`start_product_id` and @`end_product_id`

Other than that, I am not familliar with the program you are using to manage your SQL, and with that I am not familliar with some of your syntax -- that doesn't mean it's wrong, though.

you may also want to comment out the insert, and just see if your select is in fact returning any data. If not, you may have to modify your select syntax. I don't often (in fact, never) do macros like you are, where I have a.'foo' instead it's a.foo. Again, however, that may be due to the SQL manager you are using, and less a syntax issue.

[mskat02]

The tool I am using is phpadmin and this is the way it shows stored procedures. I still create them using the DDL, but when I try to edit them, this seems to be the only way to get to them.

Sorry, I thought I mentioned before that the select works fine. Very annoying, let me tell you! I will try the parens, and see if that helps.

Thx - Kat

[mskat02]

Removal of parentheses caused to change in behavior. Select still works.

[ryantroop]

I don't use phpmyadmin, So maybe someone who does may be able to help with debugging... The query looks fine, so all I can go on is assuming that it compiled without error, that your data types match, And that your query does actually return data when run inside the stored procedure. All this things true, the only other things I can think of is that your user does not have insert rights, that you must explicitly commit the insert, or some other quirk with how you are set up.

Sorry, but I think I can't help without actually connecting to the database with something like MySQL workbench and trying to run the stuff manually

[jj.dcruz]

Stored procedures will give you a small performance boost, but mostly they are for doing tasks that are difficult or impossible to do with a simple query. Stored procedures are great for simplifying access to data for many different types of clients. Database administrators love them because they control how the database is used as opposed to leaving those details to the developer.

Look to indexes and proper table design to get better performance.

[mskat02]

I agree overall on the justification for stored procedures, but that was not really my question. I'm creating a set of stored procedures to simplify an extremely complex set of steps in a critical process. In order to mitigate the risk on programming errors, I am trying to build my components of the process one-by-one. Obviously, I am missing some required components , but I am now close, thanks to the input from others on this list. I will post the completed solution when done. I can write stored procs in four other DBMS environments without a problem, but this situation had me floored! However, I am testing a fix now and will let people know how it goes.

[jj.dcruz]

DELIMITER $$

USE `techrentals`$$

DROP PROCEDURE IF EXISTS `getInvoiceList`$$

CREATE DEFINER=`root`@`%` PROCEDURE `PList`(IN prod_id INT)

BEGIN
   insert into `product_attribute` (`product_id`, `attribute_id`, `language_id`, `text`)
    SELECT 
         a.`product_id` 
            ,b.`attr_id`
            ,b.`lang_id`
            ,b.`attr_value`

    FROM     
        `product` a 
            ,`batch_attr_detail` b
            ,`batch_attr_set` c

    WHERE c.`batch_attr_id` = @`batch_attr_id`
    and c.`batch_attr_id` = b.`batch_attr_id`
    and a.`product_id` between @`start_product_id` and @`end_product_id`
END$$

DELIMITER ;

[mskat02]

I will have to try that one also. I changed the NO SQL to CONTAINS SQL and also changed the coding convention on the variables. This now works properly. Minor changes, but I really appreciate the help that you guys gave me. I followed that same convention for the next 4 stored procs and they are all working flawlessly.

Just in case someone else has my same weird setup - here is the code:

insert into `product_attribute` (`product_id`, `attribute_id`, `language_id`, `text`)

SELECT a.`product_id` 
        ,b.`attr_id`
        ,b.`lang_id`
        ,b.`attr_value`

FROM     `product` a 
        ,`batch_attr_detail` b
        ,`batch_attr_set` c

WHERE c.`batch_attr_id` = @p2
  and c.`batch_attr_id` = b.`batch_attr_id`
  and a.`product_id` between @p0 and @p1

thanks again for the help!

Kat

[mskat02]

Oops - there are three invariables. The proc would not work until I changed them to @p0, @p1 and @p2.

Kat

[ryantroop]

Glad it all worked out for you. So it was the no SQL after all