All Forums Database
teradatauser2 236 posts Joined 04/12
16 Mar 2015
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

Rohan_Sawant 55 posts Joined 07/14
17 Mar 2015

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

teradatauser2 236 posts Joined 04/12
17 Mar 2015

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

Rohan_Sawant 55 posts Joined 07/14
17 Mar 2015

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

teradatauser2 236 posts Joined 04/12
17 Mar 2015

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

Rohan_Sawant 55 posts Joined 07/14
17 Mar 2015

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

dnoeth 4628 posts Joined 11/04
17 Mar 2015

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;

 

Dieter

Rohan_Sawant 55 posts Joined 07/14
17 Mar 2015

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

LUCAS 56 posts Joined 06/09
31 Mar 2015

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

You must sign in to leave a comment.