Re: Insert Stored Procedure Help.

[Roland Bouman]

1) NVarchar is not a mysql datatype (see: http://dev.mysql.com/doc/refman/5.0/en/string-types.html). Use varchar instead, and declare with a character set specification if applicable (http://dev.mysql.com/doc/refman/5.0/en/charset-column.html shows this for columns, variable and parameter declarations adhere to the same syntax)

2) your INSERT statement does not end with a semicolon (;). THe semicolon terminates statements inside stored procedures etc:

INSERT
INTO Campers (FirstName, LastName, UserName, Password)
VALUES (cFirstName, cLastName, cUserName, cPassword)
;

3) You cannot RETURN a value from a procedure. Either declare it as a function:

CREATE function sp_InsertNewCamper
(
in cFirstName NVarChar(30),
in cLastName NVarChar(30),
in cUserName NVarChar(30),
in cPassword NVarChar(30)
)
returns int unsigned
..
..

or declare it with an OUT parameter to return the value:

CREATE procedure sp_InsertNewCamper
(
in cFirstName NVarChar(30),
in cLastName NVarChar(30),
in cUserName NVarChar(30),
in cPassword NVarChar(30),
out iIdentity int unsigned
)
begin
..
set iIdentity := last_insert_id();
end;


4) Im not sure if password is a reserved word. If it is, you may have to quote it using backticks (`):

INSERT
INTO Campers (FirstName, LastName, UserName, `Password`)
VALUES (cFirstName, cLastName, cUserName, cPassword)
;