[RESOLVED] MySQL stored proc vs MS-SQL stored proc

[WolfShade]

Hello, everyone.

I've worked with MySQL, before, but only with standard SELECT queries.

I'm now faced with a project that will require stored procedures for inserting order information (insert contact information in one table, credit card information in another table) and my MS-SQL experience is throwing me off (I keep getting syntax error messages.)

If someone could, please, give me a MySQL example of the following MS-SQL sp?

CREATE PROCEDURE order_info_sp
    @membership varchar(75),
    @fullName varchar(75),
    @company varchar(75),
    @emailAddress varchar(75),
    @ccNum varchar(20),
    @ccExpDate varchar(7),
    @ccSecCode varchar(4),
    @dateSubmit smalldatetime,
    @ipAddress varchar(15)
AS
    INSERT into thisTable (
        membership, fullName, company, emailAddress, dateSubmit, ipAddress
        )
    VALUES (
        @membership, @fullName, @company, @emailAddress, getdate(), @ipAddress
        )
    INSERT into thatTable (
        ccNum, ccExpDate, ccSecCode, dateSubmit, ipAddress
        )
    VALUES (
        @ccNum, @ccExpDate, @ccSecCode, getdate(), @ipAddress
        )
GO

Thanks,

^_^

[mathursrishti]

CREATE PROCEDURE 'order_info_sp'
(IN membership varchar(75),
IN fullName varchar(75),
IN company varchar(75),
IN emailAddress varchar(75),
IN ccNum varchar(20),
IN ccExpDate varchar(7),
IN ccSecCode varchar(4),
IN dateSubmit smalldatetime,
IN ipAddress varchar(15))
BEGIN
INSERT into thisTable (
membership, fullName, company, emailAddress,
dateSubmit, ipAddress
)
VALUES (
membership, fullName, company, emailAddress,SYSDATE,
ipAddress
)
INSERT into thatTable (
ccNum, ccExpDate, ccSecCode, dateSubmit, ipAddress
)
VALUES (
ccNum, ccExpDate, ccSecCode, SYSDATE, ipAddress
)
END

[WolfShade]

Much appreciated.. I'll give it a shot and ask questions, if I have any.

Thanks,

^_^

[WolfShade]

Actually, I just now thought of a question.

Assuming that thisTable has an auto-increment field for an ID and I wanted to relate an ID field in thatTable to the ID of thisTable, in MS-SQL I could declare a variable as INT and set that to = @@IDENTITY.

DECLARE @thisID INT
      first insert goes here
SET @thisID = @@IDENTITY
      second insert goes here and includes thatID = @thisID

I know that MySQL doesn't use @@IDENTITY. What does it use and how would I set the declared variable value for use in thatTable INSERT?

Thanks,

^_^

[chazzy]

try using LAST_INSERT_ID()

[WolfShade]

try using LAST_INSERT_ID()

SET @thisID = LAST_INSERT_ID() ?

Much appreciated,

^_^

[WolfShade]

I have tried this a few different ways, but I keep getting the same error message:

MySQL Error Number 1064
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 'INSERT into peak_trader.users_cc ('

I believe the server is MySQL 5.0.45-community-nt via TCP/IP, and the interface I am using is MySQL Administrator 1.2.12 with Client version 5.1.11.

Here is the code I am attempting to use to create a stored procedure. Any suggestions on what I missed would be greatly appreciated.

CREATE PROCEDURE peak_trader.ptp_software_order_sp
	(IN membershipOption varchar(75),
	IN firstName varchar(75),
	IN lastName varchar(75),
	IN company varchar(75),
	IN address varchar(75),
	IN city varchar(75), 
	IN state varchar(75),
	IN zip varchar(20),
	IN phone varchar(75),
	IN emailAddress varchar(75),
	IN ccType varchar(20),
	IN ccNum varchar(20),
	IN ccSecCode varchar(4),
	IN ccExpDate varchar(7),
	IN dateSubmit datetime,
	IN ipAddress varchar(15))

BEGIN
DECLARE thisID INT;

INSERT into peak_trader.users (
	user_id, first_name, last_name, status, comment, Account_Num, Cost, DTN_Password, Sales_Code, 
	membership, company, address, city, state, zip, phone, email, dateSubmit, ipAddress
)
VALUES (
	'', firstName, lastName, '', '', '', '', '', '', membershipOption, company, address, city,
	state, zip, phone, emailAddress, dateSubmit, ipAddress
)

INSERT into peak_trader.users_cc (
	CCType, CCNum, CCExpDate, CCSecNum, users_id
)
VALUES (
	ccType, ccNum, ccExpDate, ccSecCode, SELECT LAST_INSERT_ID()
)
END

^_^

[mathursrishti]

Try This,

Do not forget ';' in the syntax

CREATE PROCEDURE peak_trader.ptp_software_order_sp
(IN membershipOption varchar(75),
IN firstName varchar(75),
IN lastName varchar(75),
IN company varchar(75),
IN address varchar(75),
IN city varchar(75),
IN state varchar(75),
IN zip varchar(20),
IN phone varchar(75),
IN emailAddress varchar(75),
IN ccType varchar(20),
IN ccNum varchar(20),
IN ccSecCode varchar(4),
IN ccExpDate varchar(7),
IN dateSubmit datetime,
IN ipAddress varchar(15))

BEGIN
DECLARE thisID, newID INT;

INSERT into peak_trader.users (
user_id, first_name, last_name, status, comment, Account_Num, Cost, DTN_Password, Sales_Code,
membership, company, address, city, state, zip, phone, email, dateSubmit, ipAddress
)
VALUES (
'', firstName, lastName, '', '', '', '', '', '', membershipOption, company, address, city,
state, zip, phone, emailAddress, dateSubmit, ipAddress
);

SET newID = LAST_INSERT_ID();

INSERT into peak_trader.users_cc (
CCType, CCNum, CCExpDate, CCSecNum, users_id
)
VALUES (
ccType, ccNum, ccExpDate, ccSecCode, newID
);
END

[mattyblah]

LAST_INSERT_ID() could probably correspond to scope_identity().

[mattyblah]

also all variables in sql server require at least an @ infront of them.

[mathursrishti]

This sp is for MySql and not SqlServer

[chazzy]

I have tried this a few different ways, but I keep getting the same error message:

MySQL Error Number 1064
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 'INSERT into peak_trader.users_cc ('
~SNIP

I'm not sure why you're not trying this too

SET @thisID = LAST_INSERT_ID()

[WolfShade]

I'm not sure why you're not trying this too

SET @thisID = LAST_INSERT_ID()

It's MySQL, not MS-SQL, so I believe the '@' is not going to work.

I'll give mathursrishti's suggestion a shot; keep your fingers crossed.

Thanks,

^_^

[WolfShade]

Try This,

Do not forget ';' in the syntax

I thought I had all the semi-colons in proper place - I must have missed one or two. THIS WORKED!

Thanks, mathursrishti!!

^_^

[chazzy]

It's MySQL, not MS-SQL, so I believe the '@' is not going to work.

@thisID is a valid mysql variable, as far as I remember. but either way since you changed it in one of your later posts all I did was just copy the line you posted.

Take a look at stored proc syntax.
http://dev.mysql.com/doc/refman/5.0/...re-syntax.html