SQL - Adding user defined variables to table (mysql)?

Question

So I have this code thats part of a stored procedure:

SET @MID = 0;
SET @MX = 0;
SET @MY = 0;
SET @MT = 0;
SET @CSTAMP = '2014-6-06 08:03:19';



  SELECT @MID=m.ID, @MX=m.x , @MY=m.y , @MT=m.timestamp FROM movement m
  WHERE m.ID = cSID 
  AND m.timestamp = (SELECT MAX(T.timestamp) 
                     FROM (SELECT mm.timestamp FROM movement mm WHERE mm.ID = cSID AND mm.timestamp <= @CSTAMP)AS T);



INSERT INTO DBTable(`ctimestamp`,`ID`,`x`,`y`,`mtimestamp`) 
VALUES(ctstamp,@MID,@MX,@MY,@MT); 

Basically, it creates some user-defined variables, assigns them values in the query, and tries to insert them into a table called DBTable with columns ctimestamp,ID,x,y,mtimestamp. The problem is that when I saw the DBTable after the query,the columns ID,x,y,and mtimestamp were all empty(just 0's), and only the ctimestamp got populated with the values from cursor variable ctstamp.

I don't know whats wrong here... am I inserting into a table wrong? Can I not insert variables like @variable_name this way?

Any help would be greatly appreciated, thanks!!

EDIT

I just had a thought, since user-defined variables are session-specific, does that mean that I cannot exit out my client and turn off the computer and just let the DB server run the rest of the query? Sorry if that seems like a dumb question, but I'm very new to stored procedures & SQL.

Answer 1

In a SELECT statement, assignment to user defined variables use the (pascal-like) assignment operator (:=) rather than just an equals sign.

For example, this performs an assignment of the value 'foo' to the user defined variable:

  SELECT @var := 'foo'
              ^

That is much different than this:

  SELECT @var = 'foo'
              ^

This does not perform an assignment; it's evaluated as a boolean expression, the result of the equality comparison returns returns 0, 1 or NULL.

---

FOLLOWUP

There's no need for all those user-defined variables. You can accomplish the same result more efficiently (with fewer SQL statements)

Assuming cSID is a procedure variable, you could accomplish an equivalent result with just:

  SET @CSTAMP = '2014-6-06 08:03:19';

  INSERT INTO DBTable(`ctimestamp`,`ID`,`x`,`y`,`mtimestamp`) 
  SELECT t.ctimestamp, m.ID, m.x, m.y, m.timestamp AS mtimestamp
    FROM movement m
    JOIN ( SELECT @CSTAMP + INTERVAL 0 DAY AS ctimestamp
                , MAX(mm.timestamp) AS max_mm_timestamp
             FROM movement mm
            WHERE mm.ID = cSID
              AND mm.timestamp <= @CSTAMP + INTERVAL 0 DAY
         ) t
      ON m.ID = cSID
     AND m.timestamp = t.max_mm_timestamp
   ORDER BY 3,4
   LIMIT 1

(The user-defined variable @CSTAMP could be replaced with a procedure variable.

  DECLARE v_cstamp DATETIME DEFAULT '2014-06-06 08:03:19';

Then replace the reference to to @CSTAMP with a reference to the procedure variable... v_cstamp.

Answer 2

You are better off if you use normal variables in your stored procedure. The session variables do not have a datatype and may change during procedure execution if you happen to call other procedures which use the same session variables.

DECLARE v_mid int;
DECLARE v_mx int;
DECLARE v_my int;
DECLARE v_mt int;
DECLARE v_time datetime;

SELECT m.ID, m.x, m.y, m.timestamp INTO v_mid, v_mx, v_my, v_mt, v_time
FROM movement m
WHERE m.ID = cSID 
AND m.timestamp = (
    SELECT MAX(mm.timestamp) 
    FROM movement mm 
    WHERE mm.ID = cSID AND mm.timestamp <= v_time
);


INSERT INTO DBTable(`ctimestamp`,`ID`,`x`,`y`,`mtimestamp`) 
VALUES(v_time,v_mid,v_mx,v_my,v_mt);