ALTER PROCEDURE [dbo]. [Sp_add_jurisdiction]
@CTableName varchar (20),--current table to be deleted, new
@filedkeyValue varchar (20),--value of the field to be deleted
@filedName varchar (20),--Name of the field to be deleted
@filedNameAdd1 varchar (20),--New field name 1
@filedNameAdd2 varchar (20),--New field name 2
@sql varchar (6000)--new value-added strings, such as: 25,30;25,31
As
Begin
DECLARE @strSQL_1 varchar (6000)--delete
DECLARE @strSQL_2 varchar (6000)--New
BEGIN try
BEGIN TRANSACTION
Set @strSQL_1 = ' Delete from [' + @CTableName + '] where ' + @filedName + ' = ' + @filedkeyValue + '
EXEC (@strSQL_1)
DECLARE @Run Bit--a sign to continue cycling
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 yet empty, do the operation
Begin
Set @dotIndex =charindex ('; ', @sql)
if (@dotIndex = 0)--determine if the '; ' is found Symbol
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)--intercept ', ' before the string
Set @sql =substring (@sql, @dotIndex +1,len (@sql))--shorten the length of the string to the latter
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