I'm working on a program that extracts data from one database to another.
In several places, some text fields have to be concatenated. For instance, There are 6 addresses lines to be stored into 3 lines. This example would be easy, but there are more complicated issues: 9 text lines have to be stored into 4 text fields.
I was thinking about a general stored procedure that could look like this:
ConcatenateFields @AllTheValues, @FieldNumber, @FieldLength, @result
This function should return the substring of @AllTheValues, starting at @FieldLength * @FieldNumber
The problem is that the @AllTheValues parameter could be a string > 255. Also, I thought of declaring as Text, but I'm not allowed to do so, like its not allowed to declare a local variable of type text.
Does anyone have an idea on how to implement such a function? How can I use large strings in a stored procedure? Any ideas?