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

  • quoteSo, 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