declare and assign value my sql stored procedure(5.0.45)

Question

Hi everybody , DELIMITER $$

DROP PROCEDURE IF EXISTS quotations.sp_addservices $$ CREATE PROCEDURE quotations.sp_addservices (In categoryname varchar(25),in servicename varchar(250),in hours float,in cost float,in basis nvarchar (100)) BEGIN

insert into categorydetails (Category_Name) values (categoryname); if(categoryname!=null) then DECLARE category_id int; set category_id= select max(Category_Id) from categorydetails ; insert into servicesdetails (Service_Name,Category_Id,Hours,Cost,Basis) values(servicename,category_id,hours,cost,basis); end if; END $$

DELIMITER ;

This is my stored procedure .I have to retrive the value of categoryid that is posted into the database which is auto increased.Here i cant declare the variable and assign value to variable.Am getting error like

Script line: 4 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE category_id int; set category_id= select max(Category_Id) from categor' at line 9 Can any one help me Thanks in advance.

Answer 1

Try

SELECT MAX(c.category_id) INTO category_id FROM categorydetails c;