www.webdeveloper.com
Results 1 to 15 of 15

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

  1. #1
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582

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

    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?
    Code:
    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,

    ^_^

  2. #2
    Join Date
    Jul 2008
    Posts
    30
    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

  3. #3
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Much appreciated.. I'll give it a shot and ask questions, if I have any.

    Thanks,

    ^_^

  4. #4
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    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.
    Code:
    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,

    ^_^

  5. #5
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    try using LAST_INSERT_ID()
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  6. #6
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Quote Originally Posted by chazzy
    try using LAST_INSERT_ID()
    SET @thisID = LAST_INSERT_ID() ?

    Much appreciated,

    ^_^

  7. #7
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    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.
    Code:
    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
    ^_^

  8. #8
    Join Date
    Jul 2008
    Posts
    30
    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

  9. #9
    Join Date
    Jul 2004
    Posts
    300
    LAST_INSERT_ID() could probably correspond to scope_identity().

  10. #10
    Join Date
    Jul 2004
    Posts
    300
    also all variables in sql server require at least an @ infront of them.

  11. #11
    Join Date
    Jul 2008
    Posts
    30
    This sp is for MySql and not SqlServer

  12. #12
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    Quote Originally Posted by 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 ('
    ~SNIP
    I'm not sure why you're not trying this too

    Code:
    SET @thisID = LAST_INSERT_ID()
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

  13. #13
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Quote Originally Posted by chazzy
    I'm not sure why you're not trying this too

    Code:
    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,

    ^_^

  14. #14
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Quote Originally Posted by mathursrishti
    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!!

    ^_^

  15. #15
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    Quote Originally Posted by WolfShade
    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
    Acceptable Use | SQL Forum FAQ | celery is tasteless | twitter

    celery is tasteless - currently needing some UI time

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles