Using Dynamic databasename and tablename in stored procedure

Database
Junior Supporter

Using Dynamic databasename and tablename in stored procedure

Hi,

I have to write a query : 

Sel count(*) from databasename.tablename in a stored procedure. Now, the databasename and tablename would come as a parameter from the user while calling this SP. i tried to write :

Sel count(*) into :var1 from :databasename.:tablename, but this doesn't work.

Also, I tried running this in dbc.SysExecSQL, but this doesn't allow a select query in this.

Basically, i want to check if any table is fload locked or not ? So, a count(*) on that table will give an error 2652, if table is locked and count(*) is taken on it. So, what is the correct way of doing this ?

--Samir

8 REPLIES
Supporter

Re: Using Dynamic databasename and tablename in stored procedure

Hi teradatauser2,

The below code will do the required. You can now set a MESSAGE according to the SQLCODE you get using IF.. ELSE.

REPLACE PROCEDURE MYSP
(
IN SRC_DB_NM VARCHAR(30)
, IN SRC_TBL_NM VARCHAR(30)
, OUT MESSAGE VARCHAR(200)
)
DYNAMIC RESULT SETS 1

BEGIN

DECLARE QUERY VARCHAR(200);
DECLARE REC_COUNT INTEGER DEFAULT 0;
DECLARE STATUS CHAR(10) DEFAULT '00000';
DECLARE C1 CURSOR FOR S1;

DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING
BEGIN
SET STATUS = SQLCODE;
IF(TRIM(STATUS)) = '3807' THEN
SET MESSAGE = 'PASSED TABLE '||SRC_DB_NM||'.'||SRC_TBL_NM||' DOES NOT EXIST';
ELSE
SET MESSAGE = 'ERROR';
END IF;
END;

BEGIN
SET QUERY = 'SELECT COUNT(*) FROM ' || SRC_DB_NM || '.' || SRC_TBL_NM || ';';
PREPARE S1 FROM QUERY;
OPEN C1 USING SRC_DB_NM,SRC_TBL_NM;
FETCH C1 INTO REC_COUNT;
SET MESSAGE = REC_COUNT;
END;

END;

Thanks,

Rohan Sawant

Junior Supporter

Re: Using Dynamic databasename and tablename in stored procedure

Hi Rohan,

Thanks for the code snippet. It does get run successfully. But, it doesn't handle the current error - call failed 2652.operation not allowed, table is being loaded. This eror occurs in Open cursor stmnt. But, it deson't go to the handler section. Any idea here how to handle this error ? 

In Fact, i passed a wrong table name, the SP failed with 3807 error, but it didn't go to the error handling section and populate the Out message.

--Samir

Supporter

Re: Using Dynamic databasename and tablename in stored procedure

Hi teradatauser2,

For your comment:

In Fact, i passed a wrong table name, the SP failed with 3807 error, but it didn't go to the error handling section and populate the Out message.

Try passing a correct databasename and wrong tablename the proc will do the required.

For your comment:

Thanks for the code snippet. It does get run successfully. But, it doesn't handle the current error - call failed 2652.operation not allowed, table is being loaded. This eror occurs in Open cursor stmnt. But, it deson't go to the handler section. Any idea here how to handle this error ? 

Will work on same and let you know on the same but i guess if you put a " IF(TRIM(STATUS)) = '2652' and do the required steps you will get the requirement done.

Thanks,

Rohan Sawant

Junior Supporter

Re: Using Dynamic databasename and tablename in stored procedure

Hi Rohan,

for the 3807 error, i passed a correct dbname and a wrong tablename as you suggested. The SP fails with error 3807, but it doesn't go to the error handling session, whereas it should as i understand. The SP fails at the OPen cursor stmnt.

Thanks !

Samir

Supporter

Re: Using Dynamic databasename and tablename in stored procedure

Hi teradatauser2,

I have used the above procedure and it does the required i.e it goes in handler and throws the message. I am using TD 14.10 version. Looking at the code I am quite sure it must go. I could have added the snip here but I guess its not possible here in forum.

Looks like there is totally a different problem. There is no problem in the code for sure. May be its a version problem. Will try to debug more and let you know.

Thanks,

Rohan Sawant

Junior Contributor

Re: Using Dynamic databasename and tablename in stored procedure

Move the Handler to a nested BEGIN/END:

REPLACE PROCEDURE MYSP
(
IN SRC_DB_NM VARCHAR(30)
, IN SRC_TBL_NM VARCHAR(30)
, OUT MESSAGE VARCHAR(200)
)

BEGIN

DECLARE QUERY VARCHAR(200);
DECLARE REC_COUNT INTEGER DEFAULT 0;
DECLARE STATUS CHAR(10) DEFAULT '00000';
DECLARE C1 CURSOR FOR S1;

BEGIN
SET QUERY = 'SELECT COUNT(*) FROM ' || SRC_DB_NM || '.' || SRC_TBL_NM || ';';
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING
BEGIN
SET STATUS = SQLCODE;
IF(TRIM(STATUS)) = '3807' THEN
SET MESSAGE = 'PASSED TABLE '||SRC_DB_NM||'.'||SRC_TBL_NM||' DOES NOT EXIST';
ELSE
SET MESSAGE = 'ERROR: ' || TRIM(status);
END IF;
END;
PREPARE S1 FROM QUERY;
OPEN C1 USING SRC_DB_NM,SRC_TBL_NM;
FETCH C1 INTO REC_COUNT;
SET MESSAGE = REC_COUNT;
END;
END;

END;
Supporter

Re: Using Dynamic databasename and tablename in stored procedure

Hi Dieter,

Can you let me know whether the code given by me works or not? Because I get the desired result.

Thanks,

Rohan Sawant

Enthusiast

Re: Using Dynamic databasename and tablename in stored procedure

Hi,

this is not an answer, just an additional question about the default type and format of COUNT(*) in a query (or stored procedure) when the output type/format is not specified:

the default type is known to be INTEGER or DECIMAL according to the "transaction mode" TERADATA or ANSI of the session.

So in a "TERADATA" session a simple SELECT count(*) from "bigtable" will return an error for numeric overflow, where an "INSERT INTO" query (or stored procedure) will not, when inserting the result of COUNT(*) into column with a decimal type.

I couldn't find information about an inherited type/format for the result of count(*), supposing there is one.

Pierre