question

I'm am trying to create a stored procedure that generates the update statements to update incorrectly entered SSN with the correct SSN in over 193 tables with SSN columns one whole transaction. I want this stored proc to log the error messages and rollback the enter transaction in cases where the update fails. How do I approach this? My approach to this exercise is as listed below: if there is an efficient way of doing this, please let me know.I need quality check done on my code. Please Help!!!! CREATE PROCEDURE uspUpdateSSNErrorLog AS SET NOCOUNT ON; CREATE TABLE #UpdateSSNErrorLog ( [ErrorID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY, [ErrorNumber] [nvarchar](50) NOT NULL, [ErrorDescription] [nvarchar](4000) NULL, [ErrorProcedure] [nvarchar](100) NULL, [ErrorState] [int] NULL, [ErrorSeverity] [int] NULL, [ErrorLine] [int] NULL, [ErrorTime] [datetime] NULL ); INSERT INTO #UpdateSSNErrorLog ( ErrorNumber ,ErrorDescription ,ErrorProcedure ,ErrorState ,ErrorSeverity ,ErrorLine ,ErrorTime ) VALUES ( ERROR_NUMBER() ,ERROR_MESSAGE() ,ERROR_PROCEDURE() ,ERROR_STATE() ,ERROR_SEVERITY() ,ERROR_LINE() ,GETDATE() ); SET NOCOUNT OFF; GO CREATE PROCEDURE uspUpdateSSN AS SET NOCOUNT ON; DECLARE @OldSSN VARCHAR(9); DECLARE @NewSSN VARCHAR(9); DECLARE @CMD VARCHAR( 400); -- Create the table. CREATE TABLE #updateSSNlist ( Updatelist varchar(400) ); INSERT INTO #updateSSNlist SELECT 'UPDATE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] SET [' + Column_Name + ']=@NewSSN WHERE [' + Column_Name + ']=@OldSSN;' FROM INFORMATION_SCHEMA.COLUMNS WHERE Column_Name ='SSN'; -- Declare a cursor. DECLARE SSN_Update CURSOR FOR SELECT * FROM #updateSSNlist OPEN SSN_Update; FETCH NEXT FROM SSN_Update INTO @CMD; WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRAN BEGIN TRY EXEC @CMD; FETCH NEXT FROM SSN_Update INTO @CMD; COMMIT TRAN END TRY BEGIN CATCH EXEC uspUpdateSSNErrorLog --To log update errors ROLLBACK END CATCH CLOSE SSN_Update; DEALLOCATE SSN_Update; DROP TABLE #UpdateSSNErrorLog;