question

zillabaug avatar image
zillabaug asked

How do create a stored Procedure

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;
sql-server-2005stored-procedurestsql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
sateesh avatar image
sateesh answered
You can use after trigger to acheieve the same purpose and use table varibales to log errors into error table,since they are not affected by rollback. Further you can use inserted and deleted temp tables instead of populating temp tables . i am not sure if i understand schema of each table,if sample is posted through fiddle,i can help you further
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.