CODEWITHSUNDEEP
functiont-sqlsql-server-2012
YAKOVM
YAKOVM

Reputation: 10153

Issue while execute tsql function

I have written the following function:

CREATE FUNCTION dbo.GetData
   (@value AS VARCHAR(MAX),
    @pattern AS VARCHAR(MAX),
    @masker AS VARCHAR,
    @notMaskedCount AS INT) 
RETURNS NCHAR(47) 
AS BEGIN
   DECLARE @nextPatIdx INT
   SET @nextPatIdx = PATINDEX('%' + @pattern + '%', @value)

   WHILE @nextPatIdx > 0 AND @nextPatIdx < LEN(@value) - @notMaskedCount
   BEGIN
        SET @value = Stuff(@value, @nextPatIdx, 1, @masker)
        SET @nextPatIdx = PATINDEX('%' + @pattern + '%', @value)
   END
   RETURN CONVERT(NCHAR(40), @value) + '_data'
END

Run it with :

select dbo.GetData('152648494','[a-zA-Z0-9]', 'x', 4)

If I execute it throw C# and try readdata it throws exception If I runt it viahttp://sqlfiddle.com/#!6/6ee81/1 I get an error It doen`t complete the execution What is wrong - the function / the way I call it?

Upvotes: 0

Views: 50

Answers (1)

jean
jean

Reputation: 4350

You achieved a infinite loop as you can check using this piece of code:

---select dbo.GetData('152648494','[a-zA-Z0-9]', 'x', 4)
declare

@value AS VARCHAR(MAX) = '152648494',
@pattern AS VARCHAR(MAX) = '[a-zA-Z0-9]',
@masker AS VARCHAR(1) = 'x',
@notMaskedCount AS INT = 4 


DECLARE @nextPatIdx INT
SET @nextPatIdx = PATINDEX('%' + @pattern + '%', @value)

WHILE @nextPatIdx > 0 AND @nextPatIdx < LEN(@value) - @notMaskedCount
BEGIN
    SET @value = Stuff(@value, @nextPatIdx, 1, @masker)
    SET @nextPatIdx = PATINDEX('%' + @pattern + '%', @value)
    print @nextPatIdx;
END

print 'END'
select CONVERT(NCHAR(40), @value) + '_data'

Maybe if you change the inequality 

@nextPatIdx > 0

to

@nextPatIdx > 1

It ill exit the infinite loop but I need more info to find out what you desired output

EDIT

I guess you need to change your loop to it.

WHILE @nextPatIdx > 0 AND @nextPatIdx <= LEN(@value) - @notMaskedCount
BEGIN
    SET @value = Stuff(@value, @nextPatIdx, 1, @masker)
    --SET @nextPatIdx = PATINDEX('%' + @pattern + '%', @value)      
    SET @nextPatIdx += 1

END

Upvotes: 2

Related Questions