Mysql DECLARE variable

Hi guys,
I want to assign a value to a field

I have done this by

declare totalreferences Int default 0;
select count(transref)
INTO totalreferences
from history
WHERE storenumber = 2 and
Brandnumber = 2

I get,
> syntax error on line 1 near, 'declare totalreferences Int default 0'

If I run the query with out the first line, I get
>Error, 'undeclared variable totalreferences'


What i think Im doing -
declaring totalreferences as an int with a default of 0;
then
selecting the count(transref) , INTO totalreferences
from history
WHERE storenumber = 2 and
Brandnumber = 2



1) can i only use DECLARE inside stored procedure.
2) can someone give me an example using maybe @totalreference if this is what i need

THanks for any help
LVL 8
kingjelyAsked:
Who is Participating?
 
rjdownCommented:
For the query in your example, you can simply do:

SELECT count(transref) AS totalreferences
FROM history
WHERE storenumber = 2 AND
Brandnumber = 2

If you really do need to use variables, you'd do it something like this:

SET @totalreferences=0;
-- populate the variable
SELECT COUNT(transref) FROM history INTO @totalreferences;
-- select the variable
SELECT @totalreferences;

Note, however, that variables are only used in the scope of a connection. So if you're using mysql_connect, you'd need to change this to mysql_pconnect to start a persistent connection.

You can always do it like this, in a single line, but note that the name of the column returned would be "@totlareferences := COUNT(transref)" and not "@totalreferences":

SELECT @totalreferences := COUNT(transref) FROM history;
0
 
Chris StanyonCommented:
How about this:



select count(transref) as totalreferences
from history
WHERE storenumber = 2 and Brandnumber = 2;

Open in new window

0
 
deisrobinsonCommented:
Read more here:
http://dev.mysql.com/doc/refman/5.0/en/declare-local-variable.html

and


http://www.functionx.com/sql/Lesson03.htm

I think you'll find your problem after checking out those sites.


0
 
rjdownCommented:
Just an addition to my previous post... DECLARE is only used in stored procedures, no need to do that here.
0
 
kingjelyAuthor Commented:
Hi guys thanks,
Rj, yea i think what im after is learning how to use variables. cool thanks !

Thanks Chris, for trying.
and Deis. i will have a look thanks !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.