Bear with me
-
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? -
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
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
-
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. -
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
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).
-
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.
-
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 -
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