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:
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.
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