November 16, 2006 at 6:22 am
I'm trying to find a way to loop through 10 parameters and put each value into a temporary value for processing, so I immediately thought Dynamic SQL would work. Currently here's the code.
SET@AddCounter = 1
WHILE@AddCounter < 11
BEGIN
SET@TempSQL = 'SET@TempVal = UPPER(RTRIM(LTRIM(@Address' + CAST(@AddCounter AS Varchar(2)) + '))) + '' '''
EXEC(@TempSQL)
END
Obviously this goes out of scope but I cannot for the life of me think of any other way to actually do this where it will actually work except for using 10 seperate statements. Any ideas?
November 16, 2006 at 6:35 am
hard for me to grasp what we are trying to do....can you give examples of the parameters and also the expected output/results?
what does @Address contain?
why can't you just jump straight to @Address10 instead of looping?
Lowell
November 16, 2006 at 6:40 am
The parameters are UK address lines and I'm going to be doing some processing on them within the loop. So an example would be
@Address1 = '10 Downing Street'
@Address2 = 'Whitehall'
@Address3 = 'London'
@Address4 = 'WC10 9AA'
@Address5 = 'England'
@Address6 = ''
@Address7 = ''
@Address8 = ''
@Address9 = NULL
@Address10 = NULL
November 16, 2006 at 6:53 am
SET @AddCounter = 1
WHILE @AddCounter < 11
BEGIN
SET @TempVal = UPPER(RTRIM(LTRIM(
CASE
WHEN @AddCounter = 1 THEN @Address1
WHEN @AddCounter = 2 THEN @Address2
WHEN @AddCounter = 3 THEN @Address3
WHEN @AddCounter = 4 THEN @Address4
WHEN @AddCounter = 5 THEN @Address5
WHEN @AddCounter = 6 THEN @Address6
WHEN @AddCounter = 7 THEN @Address7
WHEN @AddCounter = 8 THEN @Address8
WHEN @AddCounter = 9 THEN @Address9
WHEN @AddCounter = 10 THEN @Address10
END)))
... code here to process @TempVal
SET @AddCounter = @AddCounter + 1
END
What is the processing per address line, maybe there is a solution without the loop
Far away is close at hand in the images of elsewhere.
Anon.
November 16, 2006 at 6:58 am
if there's not much more than uppercasing and trimming the entered values, i think i'd use 10 separate statements; it depends on what kind of processing you are doing after you uppercase/trim the values; i feel better avoiding dynamic sql unless i really need it;
Lowell
November 16, 2006 at 7:11 am
Basically, I'm stripping letter by letter and removing spurious characters and then grouping them into words which I'm storing as an array (well a table variable).
November 16, 2006 at 7:50 am
The Case statement worked and I should've thought of it myself, cheers. So, next question, when I'm breaking down the address I'm adding it into variables the same way using:
SET@TempSQL = 'SET @Item' + CAST(@ItemCount AS Varchar(2)) + ' = @TempItem'
EXEC(@TempSQL)
Where @ItemCount is another loop.
November 17, 2006 at 10:31 am
I think when I've tried to do stuff like that in the past, I've had issues with scope between the dynamic SQL and the code that's calling it.
Again a more thorough explanation of what you're trying to do would be helpful.
Rick Todd
November 20, 2006 at 2:17 am
So, next question... |
I presume this is the converse of the original problem.
You could use ten IF's or if you wanted to use dynamic sql then
EXEC sp_executesql @TempSQL,
N'@TempItem varchar(50), @Item1 varchar(50) OUTPUT, @Item2 varchar(50) OUTPUT, @Item3 varchar(50) OUTPUT, @Item4 varchar(50) OUTPUT, @Item5 varchar(50) OUTPUT, @Item6 varchar(50) OUTPUT, @Item7 varchar(50) OUTPUT, @Item8 varchar(50) OUTPUT, @Item9 varchar(50) OUTPUT, @Item10 varchar(50) OUTPUT',
@TempItem,
@Item1 OUTPUT,
@Item2 OUTPUT,
@Item3 OUTPUT,
@Item4 OUTPUT,
@Item5 OUTPUT,
@Item6 OUTPUT,
@Item7 OUTPUT,
@Item8 OUTPUT,
@Item9 OUTPUT,
@Item10 OUTPUT
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply