@@Identity trouble

[Kev]

Hi all,

I am new tomySQL (no to SQL though) and am having trouble with
something. After I insert a new record I want to get the ID of that
record into a variable (so I can use it in subsequent inserts where it
is the foreign key). However this does not work:-

INSERT ......... INTO orders .....

DECLARE @new_id INT;
SET @new_id = 7;

SET @new_id = SELECT @@IDENTITY FROM orders;
or
SELECT @@IDENTITY INTO @new_id FROM orders;

SELECT @new_id;

Can someone give me some guidance please.

Thanks

[ushastry]

Try this way..

INSERT ......... INTO orders .....

SET @new_id=LAST_INSERT_ID();

select @new_id;