try below code...
your insert statement was incorrect..
CREATE PROCEDURE sp_ResourceInsUpdDel (
@ResourceId int,
@Resource_Name varchar(50),
@Choice int)
AS
BEGIN
if(@Choice = 0)
insert into ResourceDetails(Resource_Name) values(@Resource_Name)
else if(@Choice =1)
update ResourceDetails set Resource_Name=@Resource_Name where ResourceId=@ResourceId
else if(@Choice =2)
delete from ResourceDetails where ResourceId=@ResourceId
End