I need help writing a stored procedure

How do I update several tables with columns that store social security number as a single transaction? I have over 70 tables to update the ssn with correct ssn.The database I'm working on is not normalized.

The update is strictly by request. Occasionally, I get calls for a customer who entered their ssn wrongly the first time they registered on the our website. when I get such a request, I manually update all the ssn in every database table that stores information about the customer. A very tedious task to update over 70 tables with ssn columns one by one. Does anyone have an idea how to do this efficiently ?Can someone show me how write a stored procedure that I can pass in the old SSN and the new SSN then it updates the 70 tables accordingly?

Does anyone have a blue print that demonstrates how to write the stored procedure?

TableName  ColumnName
table1      colA
table2      colB
table3      colC
table4      cold

I query the INFORMATION_SCHEMA.COLUMNS view to retrieve all the tables that have ssn columns as shown above.

March 30th, 2015 4:06pm
Save the outputs TableName and ColumnName to the table variable with a column ID IDENTITY(1, 1). And then go a loop through the table variable. For each row, create a dynamic sql to update the SSN.
Free Windows Admin Tool Kit Click here and download it now
March 30th, 2015 4:12pm

You can create a procedure that receives a ssn parameter and a key value to be searched for in every table, and then a varchar(max) variable that would store a dynamically generated SQL command based on the parameter values and the tables with the ssn column.

Example:

-- THE FOLLOWING VARIABLES ARE PARAMETERS
DECLARE @SSN VARCHAR(100) = '12354676'
DECLARE @PREDICATE VARCHAR(100) = 'A'

DECLARE @CMD VARCHAR(MAX) = '' -- THIS IS THE COMMAND VARIABLE

-- THIS WILL BUILD A SCRIPT TO UPDATE ALL TABLES WHICH CONTAIN THE SSN COLUMN
SELECT @CMD = @CMD + 'UPDATE ' + T.NAME + ' SET SSN = ''' + @SSN + ''' WHERE KEY_COLUMN = ''' + @PREDICATE + '''
GO
'
FROM SYS.TABLES T
WHERE T.OBJECT_ID IN (SELECT OBJECT_ID FROM SYS.COLUMNS WHERE NAME = 'SSN')

EXEC (@CMD);


March 30th, 2015 4:27pm

You can use Aaron's handy search stored procedure

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/06/a-handy-search-procedure.aspx

Free Windows Admin Tool Kit Click here and download it now
March 31st, 2015 1:09am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics