SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2007
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Stored Procedure syntax

    I'm used to the SQL Server world but am working with MYSQL but i can't seem to find the syntax through the documentation of MYSQL. I have this stored procedure, could somebody help me out on what i'm missing. Is it that i'm not specifying IN or OUT for the results? I'd appreciate any help.

    CREATE PROCEDURE GetDeptDetails
    (@DepartmentID int)
    AS
    SELECT Name, Desc
    FROM Table1
    WHERE DepartmentID = @DepartmentID

    This is what i had from mysql below:

    DELIMITER $$

    DROP PROCEDURE IF EXISTS `test`.`GetDepartmentDetails`$$

    CREATE PROCEDURE `test`.`GetDepartmentDetails`
    (
    @DeptID int
    )
    AS
    BEGIN

    Select ProductName, ProductDescr
    From Department
    Where DeptID = @DeptID

    END$$

    DELIMITER ;

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Where's the problem you need help with? What's it not doing that you want it to do?

    You don't need the AS keyword before BEGIN.

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2007
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It won't execute..it gives me this error.

    (0 row(s)affected)
    (0 ms taken)

    Error Code : 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 '@DeptID int)
    BEGIN
    Select DeptName, DeptDescr
    From Department
    Where DeptID = @De' at line 2
    (0 ms taken)

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Oops, didn't even notice, since I work with both SQL Server and MySQL myself. Remove the @ signs. You'll probably want to name the input parameters differently from the actual table columns to avoid confusion. I usually prefix them with in_.

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2007
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeh i hear ya man but unfortunately i'm still getting an error on this...

    DELIMITER $$
    DROP PROCEDURE IF EXISTS `test`.`GetDepartmentDetails`$$
    CREATE PROCEDURE `test`.`GetDepartmentDetails` (in_DeptID integer)
    BEGIN
    SELECT DeptName, DeptDescr
    FROM Department
    WHERE DeptID = in_DeptID
    END$$
    DELIMITER ;

  6. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Put a semicolon at the end of that SELECT query.

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2007
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    HAHA Dude your the man...thanks a lot. The damn semi-colon, i work in MS Sql environment so that's something that i totally forgot. Thanks again


Bookmarks

Posting Permissions

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