0

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!

Demetrick Norris
  • 41
  • 1
  • 2
  • 9

1 Answers1

1

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?

Community
  • 1
  • 1
Jasper
  • 444
  • 3
  • 19
  • I've tried this one. It doesn't seem to work, or I'm not using it correctly. I tried this: BEGIN declare pageexists int; declare rowcount int; select distinct ITU from tblITU where ITUsers = inuser; SELECT SQL_CALC_FOUND_ROWS * FROM tblITU; rowcount = SELECT FOUND_ROWS( ); return rowcount; END – Demetrick Norris Dec 29 '14 at 17:33
  • This one still gives the "Not allowed to return a result set from a function " error – Demetrick Norris Dec 29 '14 at 18:02
  • Sorry for that, try this: SET rowcount = (select distinct count(ITU) from tblITU where ITUsers = inuser); RETURN rowcount; – Jasper Dec 29 '14 at 18:04
  • Oops I forgot about the DISTINCT there. This should do it: SET rowcount = (SELECT COUNT(0) FROM (select distinct count(ITU) from tblITU where ITUsers = inuser) AS src); – Jasper Dec 29 '14 at 18:13
  • This one worked. Thanks. But I will admit, that I'm an idiot. I created a working solution two months ago, forgot I had it. – Demetrick Norris Dec 29 '14 at 18:27