Example of adding a data stored procedure after SQL clears table data _mssql

Source: Internet
Author: User

Copy Code code as follows:

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.