CODEWITHSUNDEEP
mysqldatabasefunctionstored-procedures
Harsh Joshi
Harsh Joshi

Reputation: 35

MYSQL Function/Procedures

I have to write function that return total number of records from table where Table name will be input.

drop function if exists totalRecord;

delimiter //
create function totalRecord(tname varachar(20))
      returns int
     begin
        declare result int;
        select count(*) into result from tname;
        return result;
    end;
//
delimiter ;

After Running Program Output is empty result set but table contains records.

Upvotes: 2

Views: 41

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13527

You have to use dynamic SQL here because SQL doesn't know the table name you are passing and functions doesn't allow dynamic sql in Mysql. So you probably need below -

drop procedure if exists totalRecord;

delimiter //
create procedure totalRecord(tname varchar(20))
     begin
        declare result int;
        declare v_Sql VARCHAR(2000);
        SET v_Tab_Name=tname;
        SET v_Sql = concat(' Select count(1) from ' , v_Tab_Name);
        PREPARE stmt FROM v_Sql;
        EXECUTE stmt ;
        DEALLOCATE PREPARE stmt;
    end;
//
delimiter ;

Upvotes: 2

Related Questions