Passing a string into a procedure for use in a IN statement

Analytics
Highlighted
Enthusiast

Passing a string into a procedure for use in a IN statement

I want to pass multiple objects list in the call but its failing but if I am passing only one object then its working pls let me know in this statement how this thing can work
Its sample code
Call sp ('objectlist=name1,name2');


REPLACE PROCEDURE proc (in text varchar (200))
DYNAMIC RESULT SETS 1
BEGIN
Declare object varchar (100);
Sel
Nvl (text ,'objectlist','=')
Into
:object
DECLARE c CURSOR WITH RETURN ONLY FOR

select case when tablekind = 'i' then 'show join index '

when tablekind = 'j' then 'show journal '

when tablekind = 'g' then 'show trigger '

when tablekind = 'p' then 'show procedure '

when tablekind = 'm' then 'show macro '

when tablekind = 't' then 'show table '

when tablekind = 'v' then 'show view '

end || trim(databasename) || '.' || trim(tablename) || ' ;' (title '')

from dbc.tables dt

where dt.tablekind in('i','j','g','p','m','t','v')

AND DATABASENAME IN

(

'db1','db2',...

)

and trim(tablename) in (:object)

group by ...
OPEN c;
END;
2 REPLIES 2
Teradata Employee

Re: Passing a string into a procedure for use in a IN statement

You can only substitute a single value for a host variable. To support a list of values you will need to concatenate the text into the SQL statement as a string, PREPARE it, and use a dynamic cursor for the prepared statement.

Ambassador

Re: Passing a string into a procedure for use in a IN statement

Hi,

There may be another way to do what you want. I think what you're trying to do is to generate a series of SHOW commands with the syntax varying based on the object type. Assuming I'm correct...

 

The following is a macro (not a stored proc) which will do this. This is coded to accept a space delimited list of values (object names). The other part of the 'trick' is to use POSITION and not a simple comparison.

REPLACE MACRO object_list (ObjectList VARCHAR(200))
AS
(
SELECT CASE WHEN tablekind = 'i' THEN 'show join index '
     WHEN tablekind = 'j' THEN 'show journal '
     WHEN tablekind = 'g' THEN 'show trigger '
	 WHEN tablekind = 'p' THEN 'show procedure '
	 WHEN tablekind = 'm' THEN 'show macro '
	 WHEN tablekind = 't' THEN 'show table '
	 WHEN tablekind = 'v' THEN 'show view '
	 end || databasename || '.' || TABLENAME || ' ;' (TITLE '')
FROM dbc.tablesv dt
WHERE dt.tablekind IN('i','j','g','p','m','t','v')
   AND DATABASENAME IN
   (
       'dbc','tdqcd'
    )
   AND POSITION(' '||TABLENAME||' ' IN ' '||:ObjectList||' ') > 0
ORDER BY databasename, TABLENAME;
);

I've also changed 'dbc.tables' to 'dbc.tablesv', this avoids the need for TRIM everywhere. I've also removed the GROUP BY, there's no need for this as the combination of databasename and tablename are unique in this view.

 

You can use the macro above or use the relevant portion of the logic (the POSITION function) in your SP.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com