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.
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:That is much different than this:
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:(The user-defined variable @CSTAMP could be replaced with a procedure variable.
Then replace the reference to to
@CSTAMP
with a reference to the procedure variable...v_cstamp
.