Example of re-adding a data storage process after clearing table data in SQL

Copy codeThe Code is as follows:
Alter procedure [dbo]. [sp_add_Jurisdiction]
@ CTableName varchar (20), -- the table to be deleted and added
@ FiledkeyValue varchar (20), -- Value of the field to be deleted
@ FiledName varchar (20), -- Name of the field to be deleted
@ FiledNameAdd1 varchar (20), -- field name 1 to be added
@ FiledNameAdd2 varchar (20), -- the field name to be added 2
@ SQL varchar (6000) -- new value-added string, for example ,;
AS
Begin
Declare @ strSQL_1 varchar (6000) -- delete
Declare @ strSQL_2 varchar (6000) -- added
BEGIN try
BEGIN TRANSACTION
Set @ strSQL_1 = 'delete from ['+ @ CTableName +'] where' + @ filedName + '=' + @ filedkeyValue +''
Exec (@ strSQL_1)

DECLARE @ Run bit -- mark of continued Loop
Declare @ dotIndex as int
Declare @ doIndex as int
Declare @ strValue1 as varchar (100)
Declare @ strValue2 as varchar (100)
Declare @ strText as varchar (100)
Declare @ ReturnValue int
Set @ Run = 1
Set @ ReturnValue = 0;
While (@ Run = 1)
Begin
If (len (@ SQL)> 0) -- if the string is not empty, perform the following operations:
Begin
Set @ dotIndex = CharIndex (';', @ SQL)
If (@ dotIndex = 0) -- determines whether the ';' symbol is found.
Begin
Set @ doIndex = CharIndex (',', @ SQL)
Set @ strValue1 = substring (@ SQL, 1, @ doIndex-1)
Set @ strValue2 = substring (@ SQL, @ doIndex + 1, LEN (@ SQL ))
Set @ strSQL_2 = 'insert into ['+ @ CTableName +'] ('+ @ filedName +', '+ @ filedNameAdd1 +', '+ @ filedNameAdd2 + ') values ('+ @ filedkeyValue +', '+ @ strValue1 +', '+ @ strValue2 + ')'
Exec (@ strSQL_2 );
Set @ ReturnValue = @ ReturnValue + 1;
Set @ Run = 0
End;
Else
Begin
Set @ strText = substring (@ SQL, 1, @ dotIndex-1) -- truncate ',' before the string
Set @ SQL = substring (@ SQL, @ dotIndex + 1, len (@ SQL) -- shorten the string length to the next one
Set @ doIndex = CharIndex (',', @ strText)
Set @ strValue1 = substring (@ strText, 1, @ doIndex-1)
Set @ strValue2 = substring (@ strText, @ doIndex + 1, LEN (@ strText ))
Set @ strSQL_2 = 'insert into ['+ @ CTableName +'] ('+ @ filedName +', '+ @ filedNameAdd1 +', '+ @ filedNameAdd2 + ') values ('+ @ filedkeyValue +', '+ @ strValue1 +', '+ @ strValue2 + ')'
Exec (@ strSQL_2)
Set @ ReturnValue = @ ReturnValue + 1;
End
End
Else
Begin
Set @ Run = 0
Set @ ReturnValue = @ ReturnValue + 1;
End
End
Commit Transaction
Return @ ReturnValue;
End try
Begin catch
Set @ ReturnValue = 0;
Rollback Transaction
Return @ ReturnValue;
End catch
End