+ Reply to Thread
Results 1 to 8 of 8

Thread: Stored Procedures on MySQL

  1. #1
    Administrator richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich's Avatar
    Join Date
    Mar 2008
    Location
    Somewhere only we know...
    Posts
    3,207
    Blog Entries
    14
    Real Name
    Rich
    Rep Power
    14

    Stored Procedures on MySQL

    I thought I'd try adding some stored procedures to my MySQL db, to see if it improves performance.

    I have a list of clients with a number of options to filter the list. At the moment I build the query as a string in .NET and then execute it on the db.

    What I am trying to do is pass the various filter options to the SP and build the query in the SP.

    How would you go about doing this in an SP?
    And, how do you pass parameters to the query inside the SP? Is it the same as you'd pass them to a query?

    I tried this simplified version from an example on the MySQL site, but it just returned "Number of rows affected:-1", which I believe means there's an error somewhere.
    Code:
    CREATE PROCEDURE clientlist
    (pcuser INT, istatus INT)
     
    BEGIN
    DECLARE _status VARCHAR (50);
    IF istatus = 5 THEN
           SET _status = ' AND visitors.status<>5';
    ELSE
           SET _status = ' AND visitors.status=1';
    END IF
     
    SET @strsql = CONCAT('SELECT visitorref,forename,surname,clientstatus.status FROM visitors,clientstatus WHERE visitors.status=clientstatus.statusref',_status);
     
    PREPARE stmt FROM @strsql;
     
    EXECUTE stmt;
     
    END;
    
    Can anyone see what I'm doing wrong or provide an example?

    Thanks

  2. #2
    Wolfmaster Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy's Avatar
    Join Date
    Mar 2008
    Location
    Peoria, IL
    Posts
    2,386
    Blog Entries
    5
    Real Name
    Wolff
    Rep Power
    15

    Does mySQL have anything like the T-SQL PRINT statement? If so, put a PRINT stmt in before the EXECUTE to check that you sql statement is correct. I notice that you don't declare strsql -- T-SQL wouldn't like this.
    Wolffy
    .-- ----- ..-. ..-. -.--
    Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Void where prohibited by law. Not valid in California. Your mileage may vary.

  3. #3
    Super Sarcasm Mistress mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere is a glorious beacon of light mehere's Avatar
    Join Date
    Mar 2008
    Location
    Wide Awake In Dreamland
    Posts
    830
    Rep Power
    8

    i don't see anything different in what you're doing, then what i'm finding.
    Code:
    DELIMITER \\
    
    CREATE PROCEDURE clientlist (
    pcuser INT, 
    istatus INT
    )
    BEGIN
    	DECLARE _status VARCHAR (50);
    	
    	IF istatus = 5 THEN
           		SET _status = 'AND visitors.status<>5';
    	ELSE
           		SET _status = 'AND visitors.status=1';
    	END IF
     
    	SET @strSQL = CONCAT('SELECT visitorref,forename,surname,clientstatus.status FROM visitors,clientstatus WHERE visitors.status=clientstatus.statusref ',_status);
    	
    	PREPARE stmt1 FROM @strSQL;
    	EXECUTE stmt1;
    	DEALLOCATE PREPARE stmt1;
    END \\
    
    DELIMITER ;
    
    to use it, just
    Code:
    CALL clientlist('pc_user',5)
    
    at least that's all it is according the mySQL manual.
    Quote of the Month:
    INSIGHT: When the going gets tough, the tough get going. The smart left a long time ago.

    Questions to Ponder:
    Are people more violently opposed to fur rather than leather because it's much easier to harass rich women than motorcycle gangs?

    iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm")
    copyright © 2008 sbenj69

    Sarchasm: The gulf between the author of sarcastic wit and the person who doesn't get it.

  4. #4
    Wolfmaster Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy's Avatar
    Join Date
    Mar 2008
    Location
    Peoria, IL
    Posts
    2,386
    Blog Entries
    5
    Real Name
    Wolff
    Rep Power
    15

    I take back what I said about strsql -- I now see that @-sign syntax is a thing called global variables -- so different from T-SQL.
    Wolffy
    .-- ----- ..-. ..-. -.--
    Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Void where prohibited by law. Not valid in California. Your mileage may vary.

  5. #5
    Administrator richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich's Avatar
    Join Date
    Mar 2008
    Location
    Somewhere only we know...
    Posts
    3,207
    Blog Entries
    14
    Real Name
    Rich
    Rep Power
    14

    Yeah, I know. I've tried all sorts of things. It creates the procedure fine, but just won't run it.

    I've even tried something really basic:-
    Code:
    CREATE PROCEDURE p4 ()
    SELECT 'Hello, world';
    
    which is straight out of a MySQL document.
    And
    Code:
    CREATE PROCEDURE p4 ()
    BEGIN
    SELECT 'Hello, world';
    END;
    
    It creates the SProc fine but when I call it, I just get Affected rows: -1.

    I've posted on the MySQL forum to see if they can suggest anything.

    Very frustrating...

  6. #6
    Administrator richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich's Avatar
    Join Date
    Mar 2008
    Location
    Somewhere only we know...
    Posts
    3,207
    Blog Entries
    14
    Real Name
    Rich
    Rep Power
    14

    And for some reason it doesn't like DELIMITER

    It says I have an error in my MySQL statement.

    *sigh*

  7. #7
    Wolfmaster Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy is a splendid one to behold Wolffy's Avatar
    Join Date
    Mar 2008
    Location
    Peoria, IL
    Posts
    2,386
    Blog Entries
    5
    Real Name
    Wolff
    Rep Power
    15

    I think that delimiter is just if you are entering the sproc from the console line by line (the equivalent of iSQL) since the semicolon is required sytanx in the sproc itself.
    Wolffy
    .-- ----- ..-. ..-. -.--
    Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Void where prohibited by law. Not valid in California. Your mileage may vary.

  8. #8
    Administrator richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich is a splendid one to behold richyrich's Avatar
    Join Date
    Mar 2008
    Location
    Somewhere only we know...
    Posts
    3,207
    Blog Entries
    14
    Real Name
    Rich
    Rep Power
    14

    OK. I think I may have sorted it.

    I downloaded the most recent MySQL Query browser, typed it into that and it executed with no problem.

    Called the SProc and it worked fine.

    Must be related to the software I was using. I found a post about a similar problem on the MySQL forum and thought I'd give it a go..

    Strange how the software you're using can affect what gets saved but there you go!!

    Shame I've wasted a whole day finding that out...

+ Reply to Thread

Similar Threads

  1. If statement stored procedure
    By peebman2000 in forum SQL Development
    Replies: 32
    Last Post: May 23rd, 2008, 04:54 PM
  2. Dynamic Stored Procedure
    By jmurrayhead in forum Microsoft SQL Server
    Replies: 16
    Last Post: March 26th, 2008, 12:19 PM
  3. Permissions on Tables, Stored Procedures, etc.
    By theChris in forum Microsoft SQL Server
    Replies: 2
    Last Post: March 24th, 2008, 12:49 PM

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

SEO by vBSEO