What?
This is a stored procedure I've nabbed from some consultants from my day job. It shuffles the records and matching data values:

Before:

ID        Name         DateOfBirth
--------- ------------ -----------
1         John Smith   1988-06-24
2         Fred Bloggs  1972-11-17
3         Another User 1964-02-18
After:
ID        Name         DateOfBirth
--------- ------------ -----------
1         Fred Bloggs  1964-02-18
2         Another User 1988-06-24
3         John Smith   1972-11-17
Looks pretty good, doesn't it? The advantages of this is that you can send this data to your developers and the data types will be correct and maybe they'll resolve issues faster than if they were given scrambled data (see my articles on DataJumble and DataScramble).

How?
CREATE PROCEDURE [Common].[usp_ScrambleMultivalue]
(
@TableName      VARCHAR(MAX),
@ColumnName     VARCHAR(MAX),
@WhereClause    VARCHAR(MAX) = NULL
)
AS
/*****************************************************************************************************************
** Author:      -                                                                                 **
** Created On:  28 August 2009                                                                                  **
** Revision:    V 0.01                                                                                          **
** Description: Stored Procedure to scramble Data in a column of a table.                                       **
**              The process will mix the Data using a random function.                                          **
**              Once the Data has been scrambled it won't be possible to get the original set of Data.          **
**                                                                                                              **
** Usage Examples:                                                                                              **
**              EXEC usp_ScrambleMultivalue 'dbo.TableA', 'ColumnNameA';                                        **
**                                                                                                              **
**              EXEC usp_ScrambleMultivalue 'dbo.TableA', 'ColumnNameA',                                        **
**                      'ColumnB = ''Text'' AND ColumnC = 15000';                                               **
**                                                                                                              **
** Inputs:                                                                                                      **
**              @TableName   : Name of the table                                                                **
**              @ColumnName  : Name of the column to be scrambled                                               **
**              @WhereClause : Condition to get the set of Data to be scrambled. Can be null                    **
**                                                                                                              **
** Outputs:                                                                                                     **
**              The Data in @ColumnName column will be scrambled                                                **
**                                                                                                              **
** Return Values:                                                                                               **
**              None                                                                                            **
**                                                                                                              **
** Before:                                                                                                      **
**      ID        Name         DateOfBirth                                                                      **
**      --------- ------------ -----------                                                                      **
**      1         John Smith   1988-06-24                                                                       **
**      2         Fred Bloggs  1972-11-17                                                                       **
**      3         Another User 1964-02-18                                                                       **
**                                                                                                              **
** After:                                                                                                       **
**      ID        Name         DateOfBirth                                                                      **
**      --------- ------------ -----------                                                                      **
**      1         Fred Bloggs  1964-02-18                                                                       **
**      2         Another User 1988-06-24                                                                       **
**      3         John Smith   1972-11-17                                                                       **
**                                                                                                              **
*****************************************************************************************************************/
BEGIN TRY

    BEGIN TRANSACTION  A

    DECLARE @NumberLeft  INT
    DECLARE @random              INT
    DECLARE @CurrentID   INT
    DECLARE @ScrambledID INT

    DECLARE @Data                VARCHAR(MAX)
    DECLARE @UpdateData  VARCHAR(MAX)
    DECLARE @Chosen              BIT
    DECLARE @ChosenID    INT
    DECLARE @SqlStatement NVARCHAR(MAX)
    DECLARE @UpdateSqlStatement NVARCHAR(MAX)
    DECLARE @UpdateCursor   CURSOR

    CREATE TABLE #Scramble
    (
            ID INT identity(1,1),
            ScrambledID INT,
            Data VARCHAR(MAX),
            Chosen      BIT
    )
    -- To dynamically insert the source Data into the temp table
    IF @WhereClause IS NULL SET @WhereClause = ' 1 = 1'

    SET @SqlStatement = '
        INSERT INTO #Scramble
        SELECT NULL,' + @ColumnName + ',NULL
        FROM ' + @TableName +
        ' WHERE ' + @WhereClause

    INSERT INTO #Scramble
    EXECUTE (@SqlStatement)

    -- To dynamically create a cursor
    SET @UpdateSqlStatement = '
        SET @UpdateCursor = CURSOR FOR
        SELECT ' + @ColumnName + ' FROM ' + @TableName + ' WHERE ' + @WhereClause +
        ' FOR UPDATE  OPEN @UpdateCursor'

    EXECUTE sp_executesql @UpdateSqlStatement, N'@UpdateCursor CURSOR OUTPUT', @UpdateCursor OUTPUT

    SELECT @NumberLeft = SUM(1)
    FROM #Scramble

    FETCH NEXT FROM @UpdateCursor INTO @Data

    WHILE @@FETCH_STATUS = 0
    BEGIN

            --  OPEN RandomID
            SET @random = CEILING(RAND()* CONVERT(VARCHAR(MAX),@NumberLeft) )

            SELECT    @ChosenID = MAX(ID )
            FROM  (SELECT TOP (@random) ID FROM #Scramble WHERE Chosen IS NULL ) A

        IF @ChosenID IS NULL RAISERROR('There are no values left to change the Data',16,1)

            UPDATE #Scramble
            SET Chosen = 1
            WHERE ID = @ChosenID

        SELECT @UpdateData = Data
        FROM #Scramble
        WHERE ID = @ChosenId

        -- To dynamically update the Data in the field being scrambled

        SET @UpdateSqlStatement = '
        UPDATE ' + @TableName + '
        SET ' + @ColumnName + ' = @UpdateDataIn WHERE CURRENT OF @UpdateCursor'

        EXECUTE sp_executesql @UpdateSqlStatement, N'@UpdateCursor CURSOR, @UpdateDataIn VARCHAR(MAX)', @UpdateCursor ,@UpdateData

        SELECT @NumberLeft = SUM(1)
        FROM #Scramble
        WHERE Chosen IS NULL

            FETCH NEXT FROM @UpdateCursor INTO @Data

    END

    CLOSE @UpdateCursor

    DEALLOCATE @UpdateCursor

    COMMIT TRANSACTION A
    --print 'committed'

END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION A
    --print 'rollback: ' + ERROR_MESSAGE()
    DECLARE @ErrorMessage   VARCHAR(MAX)

    SET @ErrorMessage = ERROR_MESSAGE()
    RAISERROR(@ErrorMessage,16,1)
END CATCH

Add comment


Send