CODEWITHSUNDEEP
mysqlstored-proceduresphpmyadminrowcount
Demetrick Norris
Demetrick Norris

Reputation: 41

Getting a row count and returning it in a variable MySQL

I have a problem where I am unable to return the number of rows in this table. I get an error when trying to store the row count in a variable and also get an error when I try to return it. This is a stored function in MySQL.

I get the two errors: 1.Not allowed to return a result set from a function 2.ROW_COUNT Doesn't exist(isn't called)

Here is my code so far:

BEGIN

declare pageexists int;
declare rowcount int;

select distinct ITU from tblITU
where ITU = inuser; 
SET rowcount = COUNT(*) FROM tblITU;

return rowcount;

END

Thanks in advance!

Upvotes: 0

Views: 14063

Answers (1)

Jasper
Jasper

Reputation: 484

For SELECTs you can use the FOUND_ROWS construct (documented here):

SELECT SQL_CALC_FOUND_ROWS something FROM your_table WHERE whatever;

SELECT FOUND_ROWS( );

which will return the number of rows in the last SELECT query (or if the first query has a LIMIT clause, it returns the number of rows there would've been without the LIMIT).

For UPDATE/DELETE/INSERT, it's the ROW_COUNT construct

INSERT INTO your_table VALUES (1,2,3);

SELECT ROW_COUNT();

which will return the number of affected rows.

Quoted from: Does Mysql have an equivalent to @@ROWCOUNT like in mssql?

Upvotes: 1

Related Questions