Procedure to get no of records from any table

[KulDeepJain]

Hi SQL experts,

Could you please help me to write below procedure :

--CREATE TYPE TABLE_TYPE AS TABLE ( "TABLE_NAME" NVARCHAR(30)  )

CREATE PROCEDURE GET_ROW_COUNT_ANY (OUT EX_COUNT INTEGER, IN TABLE_NAME TABLE_TYPE )

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

READS SQL DATA AS

BEGIN

SELECT COUNT (*) INTO EX_COUNT FROM :TABLE_NAME;

END ;

[former_member182302 · Accepted solution]

Hi KD,

What is the question actually?

In the code i wonder why have you created a "Table Type" for Table name.

PFA the code below:

CREATE PROCEDURE GET_ROW_COUNT_ANY

(IN SCHEMA_NAME VARCHAR (20),IN TABLE_NAME VARCHAR (20),OUT EX_COUNT INTEGER)

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

READS SQL DATA AS

BEGIN

SELECT RECORD_COUNT INTO EX_COUNT FROM M_TABLES

WHERE SCHEMA_NAME = :SCHEMA_NAME AND TABLE_NAME = :TABLE_NAME;

END ;

Sample Call statement:

CALL GET_ROW_COUNT_ANY ('DEMO','EMPLOYEE',?)

Regards,

Krishna Tangudu

[KulDeepJain · Accepted solution]

Hi Krishna,

Very thanks, this is really a nice way to get record count from any table. Can't we give table name as input in procedure.

[former_member182302 · Accepted solution]

You can send table name. But to do that you will have to use EXEC or EXECUTE IMMEDIATE as you are intending to frame a select query dynamically i.e Dynamic SQL.

Have a look on one blog which uses EXECUTE IMMEDIATE statement:

Don't want to use Dynamic SQL here and hence proposed you a alternate solution.

Regards,

Krishna Tangudu