Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2005
    Location
    Wexford, Ireland
    Posts
    13

    Unanswered: MySQL Stored Proc's

    The code below (in blue) works fine when run against an MS SQL table however when run through MYSQL Query Browser against a MySql copy of the table I get the following error...

    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 'declare @MyEmailString varchar(8000)

    select @MyEmailString = coalesce(@MyEmai' at line 1


    declare @MyEmailString varchar(8000)

    select @MyEmailString = coalesce(@MyEmailString + ',', '') + coalesce(FFNumber, '')
    from dbo.EMPLOYEES_Registered
    where EmpGrade = 'CMGR'

    select @MyEmailString as MailingList


    I'm using MySQL version 5.1.9 on Linux.

    Any ideas?

    Thanks,

    Dave

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you may need to have a BEGIN-END block

    when in doubt, check the manual

    see http://dev.mysql.com/doc/refman/5.1/...variables.html

    (disclaimer: i haven't written any mysql stored procs myself)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm pretty sure that you need to use GROUP_CONCAT, at least based on your original question. I'm rather surprised that R937 didn't respond there (you're slipping Rudy!).

    -PatP

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, maybe i am slipping

    i answered the question that was asked again

    i guess i failed to look deeper into the stored proc to discover that there was an even better solution, to a question that wasn't asked, namely, "is there a better way to do this?"

    of course, as pat will tell you, you should never do grouping on the server anyway, you should do it on the client side, therefore neither the stored proc nor the GROUP_CONCAT is the correct solution
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2005
    Location
    Wexford, Ireland
    Posts
    13
    Thanks guys.

    I had tried the Begin / End already without success but I can now scrap that as Group_Concat works perfectly.

    To complicate things....is it possible to do a union within a Group_Concat statement. i.e. I may need to build a list of user id's from two different tables.

    Thanks again,

    Dave

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Dave_Cha
    To complicate things....is it possible to do a union within a Group_Concat statement. i.e. I may need to build a list of user id's from two different tables.
    i'm sure it can be done, but perhaps not with the UNION actually within the GROUP_CONCAT

    but why are you using mysql? pat says it isn't relational, so maybe you'd better reconsider, and just stick with SQL Server
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2005
    Location
    Wexford, Ireland
    Posts
    13
    All our db's currently sit on MySQL. We will be migrating to MS SQL but only gradually and in the meantime I have to find a solution for MySQL.

    I'm in the process of setting up a number of DTS packages on the MS SQL server which pull data from MySQL. When I was testing the 'Coalesce' solution earlier I copied the required tables to the MS SQL server temporarily.

    If you can think of how I could merge the results of two tables on MySQL it would be a great help. I tried a simple union however that only gave me two rows of data.

    Thanks,

    Dave

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Dave_Cha
    If you can think of how I could merge the results of two tables on MySQL it would be a great help. I tried a simple union however that only gave me two rows of data.
    i would use UNION
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2005
    Location
    Wexford, Ireland
    Posts
    13
    I've tried UNION but it returns two rows of data and I need one row.

    select group_concat(FFNumber)
    FROM ERRORS_AccessControl
    WHERE Role = 'MGR'
    UNION
    select group_concat(FFNumber)
    FROM EMPLOYEES_Registered
    WHERE Role = 'MGR'

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select group_concat(FFNumber)
      from (
           select FFNumber
             from ERRORS_AccessControl
            where Role = 'MGR'
           union
           select FFNumber
             from EMPLOYEES_Registered
            where Role = 'MGR'
           ) as foo
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2005
    Location
    Wexford, Ireland
    Posts
    13
    Brilliant....thanks so much for all the help. Hope I can return the favour sometime. Dave

Posting Permissions

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