stored procedure

[xxmp]

I have made this stored procedure

DELIMITER //
DROP PROCEDURE IF EXISTS find_max;
CREATE PROCEDURE find_max
(IN type INT,OUT maxx INT)
BEGIN
SELECT max(card_id)+1 INTO maxx FROM cards;
END;
//
DELIMITER;

and i want to have this stored procedure
DELIMITER //
DROP PROCEDURE IF EXISTS add_card;
CREATE PROCEDURE add_card
(var_member_id INT,var_card_number VARCHAR(16),var_code INT,var_expireday INT,var_expireyear INT, var_type INT)
BEGIN
INSERT INTO cards (member_id,card_number,code,expireday,expireyear,type,card_id,valid) VALUES (var_member_id,var_card_number,var_code,var_expireday,var_expireyear,var_type,var_card_id,1);
END;
//
DELIMITER;

where var_card_id will be the value that return the procedure find_max
How i can do this?
Thank you very!!

[smantscheff]

Convert your first procedure to a function which returns the desired value and call it from your 2nd procedure.
Apart from that this looks like nonsense to me.
What for do you need a stored procedure which does nothing than insert a row into a table?

[xxmp]

it is a non sence project in a lesson a have:S. Can you tell me how i can do this?

[smantscheff]

Study this: http://dev.mysql.com/doc/refman/5.1/en/stored-procedure-syntax.html

[sunwebsite]

If you want get the [cart_id + 1],
after you inserted your value in your stored procedure
better you write

SELECT LAST_INSERT_ID() + 1;

[xxmp]

OK thank you very much. Now i have another problem

DELIMITER //
DROP PROCEDURE IF EXISTS movies;
CREATE PROCEDURE movies
(var_group_by INT,var_filter INT,var_type INT,var_lookfor1 VARCHAR(80), var_lookfor2 VARCHAR(80),OUT X12 vARCHAR(300))
BEGIN
    DECLARE lookat VARCHAR(80);
    DECLARE looking1 VARCHAR(100);
    DECLARE looking2 VARCHAR(100);
    DECLARE more VARCHAR(100);
    DECLARE sql1 VARCHAR(300);
    DECLARE sql2 VARCHAR(300);
    IF var_filter=0 THEN
	      SET sql1="SELECT * FROM movies ";	      
    ELSEIF var_filter=1 THEN
	      SET lookat="title";
	      SET looking1="%"+var_lookfor1+"%";
	      SET sql1="SELECT * FROM movies ";
    ELSEIF var_filter=2 THEN
	      SET lookat="director";
	      SET sql1="SELECT title,releaseyear,rate,copies,price,movies.movie_id FROM movies,directors ";
	      SET looking1="%"+var_lookfor1+"%";
	      SET more=" AND movies.movie_id=directors.movie_id";
    ELSEIF var_filter=3 THEN
	      SET lookat="actor";
	      SET sql1="SELECT title,releaseyear,rate,copies,price,movies.movie_id FROM movies,actors ";
	      SET more=" AND movies.movie_id=actors.movie_id";
	      SET looking1="%"+var_lookfor1+"%";
    ELSEIF var_filter=4 THEN
	      SET lookat="rate";
	      SET sql1="SELECT * FROM movies ";
    ELSEIF var_filter=5 THEN
	      SET lookat="price";
	      SET sql1="SELECT * FROM movies ";
    ELSEIF var_filter=6 THEN
	      SET lookat="category";
	      SET looking1="%"+var_lookfor1+"%";
	      SET sql1="SELECT * FROM movies ";
    END IF;
    if (var_filter) THEN
           if (var_type=1) THEN
                if (var_filter) THEN
	            SET lookat="directors.director";
                END IF;
                if ((var_filter=1) || (var_filter=2) || (var_filter=3) || (var_filter=6)) THEN
		   SET sql1=sql1+"WHERE "+lookat+" LIKE '"+looking1+"' ";
                ELSE
		    SET sql1=sql1+"WHERE "+lookat+"="+var_lookfor1;	
                END IF;	
            ELSE
	        SET sql1=sql1+"WHERE "+lookat+">="+var_lookfor1+" AND "+lookat+"<="+var_lookfor2+"";
            END IF;
     END IF; 
    SET sql1=sql1+more;
    IF var_group_by=1 THEN
	 SET sql1=sql1+" ORDER BY releaseyear DESC";
    ELSEIF var_group_by=2 THEN
	 SET sql1=sql1+" ORDER BY title DESC";
    ELSEIF var_group_by=3 THEN
	 SET sql1=sql1+" ORDER BY director DESC";
    ELSEIF var_group_by=4 THEN
	 SET sql1=sql1+" ORDER BY rate DESC";
    ELSEIF var_group_by=5 THEN
	 SET sql1=sql1+" ORDER BY  price DESC";
    ELSEIF var_group_by=6 THEN
	 SET sql1=sql1+" ORDER BY copies DESC ";
    ELSEIF var_group_by=1 THEN
	 SET sql1=sql1+" ORDER BY category DESC ";
    END IF;
    EXECUTE sql1;
END;
//
DELIMITER;

The sql1 is always NULL what is the problem? I cant'w find why when i am doing this SET sql1='hi '+'bro'; the result of sql1 is NULL :S
Thank you very much!

[sunwebsite]

I thing your requirement is concatenating the result with the order by.
You should not use arithmetic operator like ( + ) for concatenating the string in mysql code. Follow the below to concatenate your string.

SET sql1 = CONCAT(sql1, " ORDER BY Your_Reqirement DESC");

[xxmp]

Thank you very much