PDA

Click to See Complete Forum and Search --> : [RESOLVED] MySQL stored proc vs MS-SQL stored proc

WolfShade
07-15-2008, 09:29 PM
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
07-16-2008, 01:58 AM
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
07-16-2008, 03:17 PM
Much appreciated.. I'll give it a shot and ask questions, if I have any.

Thanks,

^_^
WolfShade
07-16-2008, 03:45 PM
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
07-16-2008, 10:23 PM
try using LAST_INSERT_ID()
WolfShade
07-17-2008, 01:36 PM
try using LAST_INSERT_ID()
SET @thisID = LAST_INSERT_ID() ?

Much appreciated,

^_^
WolfShade
07-17-2008, 02:04 PM
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
07-18-2008, 01:17 AM
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
07-18-2008, 01:45 AM
LAST_INSERT_ID() could probably correspond to scope_identity().
mattyblah
07-18-2008, 01:47 AM
also all variables in sql server require at least an @ infront of them.
mathursrishti
07-18-2008, 02:08 AM
This sp is for MySql and not SqlServer
chazzy
07-18-2008, 09:49 AM
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
07-18-2008, 12:11 PM
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
07-18-2008, 12:15 PM
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
07-18-2008, 03:11 PM
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/en/stored-procedure-syntax.html