Stored Procedure Scenarion

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Fan

Stored Procedure Scenarion

Hi Guys,

I have below actions to be performed inside a stored procedure. Are they possible?

1) A string parameter as an input (R1|R2|R3|R4) -- Well Yes I think.
2) Next, create a volatile table and store the above parameter's values as rows by transposing it and also put a row number as second column against each value. To illustrate, the above parameter should look like below; if it goes into a table:

COLUMN_NM|COLUMN_NUMBER
R1|1
R2|2
R3|3
R4|4

Note:- The above data illustrates 2 columns and 4 rows.

3) Read the MAX(COLUMN_NUMBER) and store the "COLUMN_NM" value in a variable.


I tried doing it but going clueless everywhere. Any help will be appreciated.

 

Thanks.
GV

2 REPLIES
Senior Apprentice

Re: Stored Procedure Scenarion

The STRTOK_SPLIT_TO_TABLE table function can be used to split a delimited string into a result set.

 

Of couse you can use it in a SP, in fact you can get your result with a single SELECT:

SELECT TOP 1 token
FROM TABLE
 ( StrTok_Split_To_Table( 1 , 'R1|R2|R3|R4', '|'  ) 
   RETURNS 
    ( outkey INT
     ,tokennum INT
     ,token VARCHAR(20)
    ) 
 ) dt
ORDER BY tokennum DESC 

 

Fan

Re: Stored Procedure Scenarion

Thanks for the help!