CODEWITHSUNDEEP
t-sqlsql-server-2012user-defined-functions
Matt Hall
Matt Hall

Reputation: 2412

Wrong value assigned to variable

I've started drafting a scalar function in SSMS 2012 to take a string and swap any occurrence of 3 hex URL characters (%2b, %2f and %3d) with their respective single character (-, / and =):

DECLARE @OutputText as VARCHAR(100)
DECLARE @c as Int   
DECLARE @cIn as CHAR(3)
DECLARE @cOut as CHAR(1)
DECLARE @s as Int

SET     @OutputText = '%2bBWCq6sE7OU%3d'
SET     @c = 1

WHILE @c <= 3
    BEGIN
        -- Set the search/replace characters depending on iteration
        IF  @c = 1
            SET @cIn = '%2b'
            SET @cOut = '-';
        IF  @c = 2
            SET @cIn = '%2f'
            SET @cOut = '/';
        IF  @c = 3
            SET @cIn = '%3d'
            SET @cOut = '=';

        SET @s = PATINDEX('%' + @cIn +'%', @OutputText)

        WHILE @s > 0
            BEGIN

                PRINT 'Character Loop: ' + CAST(@c as VARCHAR(1)) + ' | looking for ' + @cIn + ' within ' + @OutputText
                PRINT '(Replace ' + @cIn + ' with ' + @cOut + ')'
                PRINT '-- ' + @cIn + ' found at position: ' + CAST(@s as VARCHAR(2))

                SET @OutputText = STUFF(@OutputText, PATINDEX('%' + @cIn +'%', @OutputText) - 1, 3, @cOut)

                PRINT '>> OutputText now: ' + @OutputText + CHAR(10)

                SET @s = PATINDEX('%' + @cIn +'%', @OutputText)

            END

    SET     @c = @c + 1

    END

PRINT 'Final output: ' + @OutputText

The various PRINTs return this:

enter image description here

Notice the first character loop output says Replace %2b with = ... yet the if statement should be setting @cOut to - not = when @c = 1.

Another minor issue is that where the output says %2b found at position: the position number given seems 1 higher than it should be, like it's ignoring % of @cIn.

Upvotes: 0

Views: 67

Answers (1)

GandRalph
GandRalph

Reputation: 645

In your If statements, each setting of @cout will happen because they are not part of the IF. Only the next line after the IF executes. You need to wrap them in Begin End:

IF  @c = 1
begin
    SET @cIn = '%2b'
    SET @cOut = '-'
end
else IF  @c = 2
begin
    SET @cIn = '%2f'
    SET @cOut = '/'
end
else IF  @c = 3
begin
    SET @cIn = '%3d'
    SET @cOut = '='
end

To prove this:

declare @thing int = 2

if @thing=1
select 'ralph'
select 'charles'

if @thing = 2
select 'ralph2'
select 'charles2'

This will produce charles, ralph2, charles2

Whereas this (with begin end):

declare @thing int = 2

if @thing=1
begin
    select 'ralph'
    select 'charles'
end

if @thing = 2
begin
    select 'ralph2'
    select 'charles2'
end

will correctly produce ralph2, charles2

Upvotes: 1

Related Questions