This is a stored procedure I've nabbed from some consultants from my day job. It shuffles the records and matching data values:
Before:
copyraw
After:ID Name DateOfBirth --------- ------------ ----------- 1 John Smith 1988-06-24 2 Fred Bloggs 1972-11-17 3 Another User 1964-02-18
- ID Name DateOfBirth
- --------- ------------ -----------
- 1 John Smith 1988-06-24
- 2 Fred Bloggs 1972-11-17
- 3 Another User 1964-02-18
copyraw
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).ID Name DateOfBirth --------- ------------ ----------- 1 Fred Bloggs 1964-02-18 2 Another User 1988-06-24 3 John Smith 1972-11-17
- ID Name DateOfBirth
- --------- ------------ -----------
- 1 Fred Bloggs 1964-02-18
- 2 Another User 1988-06-24
- 3 John Smith 1972-11-17
How?
copyraw
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
- 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
Category: Transact-SQL :: Article: 459
