3

I got this procedure :

DELIMITER $$
CREATE PROCEDURE `countRows`(IN v varchar(30))
BEGIN
   SET @t1 =CONCAT("SELECT COUNT(*) FROM  ",V);
   PREPARE stmt3 FROM @t1;
   EXECUTE stmt3;
   DEALLOCATE PREPARE stmt3;
END$$
DELIMITER ;

And I want this to return the query in this format : "The table X contains Y Rows" I tried to use the concat function but it just don't work for me.

Some tips? Thanks

Edw4rd
  • 147
  • 1
  • 9
  • I edit the question, sorry if it wasn't clear enought. – Edw4rd Jun 04 '15 at 17:29
  • Well you shouldn't have taken out part of your query. The question is clear, what I meant is: are you getting an error? Does it return incorrect results? I just want to understand how/why it isn't doing what you need it to. – AdamMc331 Jun 04 '15 at 17:32
  • The syntax of the old procedure was fine but When I call the procedure and give by parameters the name of the table that I wanted it just didn't work. – Edw4rd Jun 04 '15 at 17:41

1 Answers1

1

I was able to make this work by using a subquery. I couldn't find any way to do the concat and count search at the same time, but by wrapping the count into a subquery and then using that value in the select clause I was able to return the expected results. Try this:

SELECT CONCAT("The table contains ", tmp.numRows, " rows.")
FROM(
  SELECT COUNT(*) AS numRows
  FROM myTable) tmp;

Here is an SQL Fiddle example of the query by itself, not as a prepared statement.

AdamMc331
  • 16,492
  • 10
  • 71
  • 133
  • This works and is what I want but I don't know how to impement it to my procedure, some help? – Edw4rd Jun 04 '15 at 17:49
  • @Edw4rd I don't use a lot of stored procedures, but I will tinker with it when I can. Let me know if you have any more trouble. – AdamMc331 Jun 04 '15 at 19:07