title: MySQL several queries using if
body: <p>I am right now writing a few MySQL commands, which allow me to generate better statistics. My problem is now that under special circumstances a few queries should be called and under other circumstances not.</p>
  
  <p>my current attempt: (I shorted it)</p>
  
  <pre><code>SET @var1=0, @var2=0;
  SELECT @var1 := `id` FROM `sg_playerstats` ORDER BY `id` ASC LIMIT 0, 1;
  SELECT @var2 := `Last Updated` FROM `sg_playerranking` LIMIT 0, 1;
  IF @var1 &gt; @var2 THEN (
  
  TRUNCATE `sg_playerranking`;
  
  SET
      @Rank := 0;
  
  INSERT INTO
      `sg_playerranking`
  SELECT
      @Rank := @Rank + 1 AS `Rank`,
      t.*,
      @var1 AS `Last Updated`
  FROM
      (
          SELECT
              `player` AS `Player`,
          FROM
              `sg_playerstats`
          GROUP BY
              `player`
          ORDER BY
              `Score` DESC, ...
      ) t;  )
  END IF
  
  SELECT `Rank`, ... FROM `sg_playerranking` WHERE 1
  </code></pre>
  
  <p>The If statement is not working (Syntax error. Every single query part itself is working!)
  I did some research but could not find anything but that!</p>
  
  <p>The parentheses make no difference!</p>
  
  <p>EDIT:</p>
  
  <p>Now i am really confused!</p>
  
  <p>My code is:</p>
  
  <pre><code>CREATE PROCEDURE generate_statistics (OUT param1 INT)
  BEGIN
  
      SET @var1=0, @var2=0;
      SELECT @var1 := `id` FROM `sg_playerstats` ORDER BY `id` ASC LIMIT 0, 1;
      SELECT @var2 := `Last Updated` FROM `sg_playerranking` LIMIT 0, 1;
      IF @var1 &gt; @var2 THEN
  
      TRUNCATE `sg_playerranking`;
  
      SET
          @Rank := 0;
  
      INSERT INTO
          `sg_playerranking`
      SELECT
          @Rank := @Rank + 1 AS `Rank`,
          t.*,
          @var1 AS `Last Updated`
      FROM
          (
              SELECT
                  `player` AS `Player`,
                  SUM(1) AS `Games`,
                  SUM(`points`) AS `Points`,
                  SUM(`points`) / SUM(1) AS `Points per Game`,
                  SUM(IF(`position` = 1, 1, 0)) AS `Wins`,
                  SUM(IF(`position` = 1, 1, 0)) / SUM(1) AS` Wins per Game`,
                  SUM(IF(`position` = 1, 0, 1)) AS `Loses`,
                  SUM(IF(`position` = 1, 0, 1)) / SUM(1) AS `Loses per Game`,
                  SUM(`kills`) AS `Kills`,
                  SUM(`kills`) / SUM(1) AS `Kills per Game`,
                  SUM(`death`) AS `Deaths`,
                  SUM(`death`) / SUM(1) AS `Deaths per Game`,
                  SEC_TO_TIME(SUM(`time`) / 1000) AS `Time played`,
                  ROUND(sqrt(SUM(`points`)) + 10 * SUM(`points`) / SUM(1) + 10 * sqrt(SUM(IF(`position` = 1, 1, 0))) + 100 * SUM(IF(`position` = 1, 1, 0)) / SUM(1) - 5 * sqrt(SUM(IF(`position` = 1, 0, 1))) - 50 * SUM(IF(`position` = 1, 0, 1)) / SUM(1) + 7.5 * sqrt(SUM(`kills`)) + 75 * SUM(`kills`) / SUM(1) - 3.75 * sqrt(SUM(`death`)) - 37.5 * SUM(`death`) / SUM(1), 2) AS `Score`
              FROM
                  `sg_playerstats`
              GROUP BY
                  `player`
              ORDER BY
                  `Score` DESC,
                  `Points per Game` DESC,
                  `Wins per Game` DESC,
                  `Loses per Game` ASC,
                  `Kills per Game` DESC,
                  `Deaths per Game` ASC,
                  `Points` DESC,
                  `Wins` DESC,
                  `Loses` ASC,
                  `Kills` DESC,
                  `Deaths` ASC,
                  `Games` DESC,
                  `Time played` DESC,
                  `Player` DESC
          ) t;
      END IF;
  
      SELECT `Rank`, `Player`, `Games`, `Points`, `Points per Game`, `Wins`, `Wins per Game`, `Loses`, `Loses per Game`, `Kills`, `Kills per Game`, `Deaths`, `Deaths per Game`, `Time played`, `Score` INTO param1 FROM `sg_playerranking` WHERE 1
  END;
  </code></pre>
  
  <p>This is the error i get:</p>
  
  <pre><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 '' at line 4
  </code></pre>
  
  <p>Well it makes no difference if I take DECLARE!</p>
  
  <p>(Sorry for the long query!)</p>
  
  <p>EDIT2:</p>
  
  <p>I finally solved it! This code is working!</p>
  
  <pre><code>DELIMITER //
  
  CREATE PROCEDURE gamestats ()
  BEGIN
      SET
          @var1 = 1,
          @var2 = 0;
  
      SELECT
          @var1 := `id`
      FROM
          `sg_playerstats`
      ORDER BY
          `id` DESC
      LIMIT
          0,
          1;
  
      SELECT
          @var2 := `Last Updated`
      FROM
          `sg_playerranking`
      LIMIT
          0,
          1;
  
      IF
          @var1 &gt; @var2
      THEN
          TRUNCATE
              `sg_playerranking`;
          SET
              @Rank := 0;
          INSERT INTO
              `sg_playerranking`
          SELECT
              @Rank := @Rank + 1 AS `Rank`,
              t.*,
              @var1 AS `Last Updated`
          FROM
              (
                  SELECT
                      `player` AS `Player`,
                      SUM(1) AS `Games`,
                      SUM(`points`) AS `Points`,
                      SUM(`points`) / SUM(1) AS `Points per Game`,
                      SUM(IF(`position` = 1, 1, 0)) AS `Wins`,
                      SUM(IF(`position` = 1, 1, 0)) / SUM(1) AS` Wins per Game`,
                      SUM(IF(`position` = 1, 0, 1)) AS `Loses`,
                      SUM(IF(`position` = 1, 0, 1)) / SUM(1) AS `Loses per Game`,
                      SUM(`kills`) AS `Kills`,
                      SUM(`kills`) / SUM(1) AS `Kills per Game`,
                      SUM(`death`) AS `Deaths`,
                      SUM(`death`) / SUM(1) AS `Deaths per Game`,
                      SEC_TO_TIME(SUM(`time`) / 1000) AS `Time played`,
                      ROUND(sqrt(SUM(`points`)) + 10 * SUM(`points`) / SUM(1) + 10 * sqrt(SUM(IF(`position` = 1, 1, 0))) + 100 * SUM(IF(`position` = 1, 1, 0)) / SUM(1) - 5 * sqrt(SUM(IF(`position` = 1, 0, 1))) - 50 * SUM(IF(`position` = 1, 0, 1)) / SUM(1) + 7.5 * sqrt(SUM(`kills`)) + 75 * SUM(`kills`) / SUM(1) - 3.75 * sqrt(SUM(`death`)) - 37.5 * SUM(`death`) / SUM(1), 2) AS `Score`
                  FROM
                      `sg_playerstats`
                  GROUP BY
                      `player`
                  ORDER BY
                      `Score` DESC,
                      `Points per Game` DESC,
                      `Wins per Game` DESC,
                      `Loses per Game` ASC,
                      `Kills per Game` DESC,
                      `Deaths per Game` ASC,
                      `Points` DESC,
                      `Wins` DESC,
                      `Loses` ASC,
                      `Kills` DESC,
                      `Deaths` ASC,
                      `Games` DESC,
                      `Time played` DESC,
                      `Player` DESC
              ) t;
      END IF;
  END;
  //
  DELIMITER ;
  </code></pre>