on 02-15-2014 4:31 PM
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 ;
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
93 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.