Data Masking in SQL Server 2008

Question

I'm trying to mask all other ID's when a specific ID is selected. But it will not run. This is what I have so far.

This is my current code:

USE [DB1]
Declare IDCursor Cursor
    For
        Select ID, Year, Amount, RefreshDate 
        From Table1
        For Update
    Open IDCursor
    Declare @ID int, @Year datetime, @Amount money, @RefreshDate datetime
    Fetch Next From IDCursor 
    Into @ID, @Year, @Amount, @RefreshDate 
    While @@Fetch_Status = 0
Begin
    If @ID <> 0
    SELECT SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 5)

    Else
    Set @ID = 0

End
Close IDCursor 
Deallocate IDCursor

What error are you getting? – Mike Stockdale Apr 08 '14 at 13:52 — Mike Stockdale, Apr 08 '14 at 13:52
nothing shows, it just constantly runs – user3508195 Apr 08 '14 at 14:44 — user3508195, Apr 08 '14 at 14:44

score 0 · Answer 1 · answered Apr 08 '14 at 13:59

0

Not sure if this is just a typo? Did you want to do the below set @ID differently?

Begin
    If @ID <> 0
    Set @ID = SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 5)
   --OR MAYBE SELECT @ID = SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 5)

Else
    Set @ID = 0

End

answered Apr 08 '14 at 13:59

Anthony Horne

2,522
2
29
51

it just keeps saying 'executing query'. it's been running for almost an hour already – user3508195 Apr 08 '14 at 14:48
Cursors are the way I generally steer clear of. Can you not do the update as part of a set based, i.e. update Table1 set ID = Case when ID = 0 THEN 0 else SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 5) end – Anthony Horne Apr 08 '14 at 15:26

score 0 · Answer 2 · answered Apr 08 '14 at 18:38

0

Your loop condition While @@Fetch_Status = 0 never changes so you have an infinite loop. You need to keep fetching inside the loop:

While @@Fetch_Status = 0
Begin
    ...
    Fetch Next From IDCursor Into @ID, @Year, @Amount, @RefreshDate 
End

answered Apr 08 '14 at 18:38

Mike Stockdale

5,256
3
29
33

Data Masking in SQL Server 2008

2 Answers2