variables in stored proc

[nickdel]

I'm having problems with the following:

create procedure UpdateOrder ( 
@PID as int, 
@COrder as int, 
@NOrder as int) 
begin 

declare OID int 
select ID into OID from Playlist where Order_ID = @NOrder 

update Playlist 
set Order_ID = @NOrder 
where ID = @PID 

update Playlist 
set Order_ID = @COrder 
where ID = OID 

end;

I've only ever worked with SQL Server in the past and I just can't seem to get my head around MySQL stored procs. I get an syntax error (select' at line 2). Can anyone point me in the right direction?

Thanks

Nick

 

[obadare]

You can try this link:

http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html

 

[nickdel]

Thanks obadare, I've had a look at the general syntax on the mysql website and I believe what I have should be correct but it would appear as though I cannot set variables within the stored procedure (is this correct?). I was hoping someone could debug my code?

Thanks

Nick

 

[hvass]

At least he is up front -
>>I was hoping someone could debug my code?
nickdel - I am afraid you will have trouble convincing too many posters on this site that you have read the manual.. MySQL statements are terminated with a ; page 2 of the tutorial hence your error select' at line 2 means MySQL is saying I do not understand the statement "declare OID int select"

 

[nickdel]

haha, fair enough. Any consolation I reply to a ton of posts on here also, just never had to work with mySQL.

I have tried setting the delimiter to | and then this

delimiter |
create procedure UpdateOrder ( 
@PID as int,
@COrder as int,
@NOrder as int)
begin

declare OID int;
select ID into OID from Playlist where Order_ID = @NOrder;

update Playlist
set Order_ID = @NOrder
where ID = @PID;

update Playlist
set Order_ID = @COrder
where ID = OID;

end|

but then I just got "Undeclared variable: OID"

I have been trying!

Cheers

Nick