通用自動為某表某欄位生成複製名稱的儲存過程

當在表tab(id, name...)中生成一條新紀錄，其他欄位都與原紀錄一致，但name欄位必須按name, Copy of name, Copy (2) of name, Copy (3) of name的規律變化，用下面的SP可以輕鬆達到此目的。

它是通用的，可以針對任何表的varchar(char)型別的欄位，生成新名稱。

-- SP_generate_copy_object_name
-- ============================================
-- Procedure Name : SP_generate_copy_object_name
-- Function   : generate a object name that is copied
-- Failure return : ''
-- Success return : the new object name
--
-- Parameters  :
-- @table_name  :
-- @field_name  :
-- @field_value  : the value of the field
-- @field_length : the length of the field
--
-- Description:
-- original field name 'ProductReport' --&gt 'Copy of ProductReport' --&gt 'Copy (2) of ProductReport' --&gt 'Copy (3) of ProductReport'... --&gt 'Copy (9999) of ProductReport'...
-- ============================================

IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'SP_generate_copy_object_name') and objectproperty(id, N'IsProcedure') = 1)
 DROP PROCEDURE SP_generate_copy_object_name
GO

DROP PROCEDURE SP_generate_copy_object_name
GO
CREATE PROCEDURE SP_generate_copy_object_name
 (
 @table_name   varchar(128),
 @field_name   varchar(128),
 @field_value  VARCHAR(128),
 @field_length  INT = 128,
 @new_object_name varchar(128) OUTPUT
 )
--WITH ENCRYPTION
AS
DECLARE
 @exists    INT,
 @tmp_field_value VARCHAR(128),
 @sql     NVARCHAR(500),
 @i      INT
BEGIN
 
 -- Check input parameter field_value
 IF (@field_value IS NULL) OR (LTRIM(@field_value) = '')
 BEGIN
  RETURN 1
 END
 
 --  you need at least use the first five characters of object name
 
 -- 1. search 'Copy of ' + @field_value
 SET @tmp_field_value = @field_value
 IF len(@tmp_field_value) > @field_length - 8
 BEGIN
  SET @tmp_field_value = SUBSTRING(@tmp_field_value, 1, @field_length - 11) + '...'
 END
 ELSE
 BEGIN
  SET @tmp_field_value = @tmp_field_value
 END
 
 SET @sql = N'drop table ##tab_tmp create table ##tab_tmp(exist_flag int) insert into ##tab_tmp(exist_flag) SELECT count(1) FROM ' + @table_name + ' WHERE ' + @field_name + ' LIKE ''Copy of ' + @tmp_field_value + ''''
 EXEC SP_EXECUTESQL @sql

 select @exists = exist_flag from ##tab_tmp
 IF @exists = 0
 BEGIN
  SET @new_object_name = 'Copy of ' + @tmp_field_value
  RETURN 0
 END

 -- 2. search N'Copy (%) of ' + @field_value
 SET @i = 2
 WHILE ( @i < 9999 )
 BEGIN
  SET @tmp_field_value = @field_value
  IF len(@tmp_field_value) > @field_length - (11 + LEN(@i))
  BEGIN
   SET @tmp_field_value = SUBSTRING( @tmp_field_value, 1, @field_length - (11 + LEN(@i) + 3) ) + '...'
  END
  ELSE
  BEGIN
   SET @tmp_field_value = @tmp_field_value
  END
  SET @sql = N'drop table ##tab_tmp create table ##tab_tmp(exist_flag int) insert into ##tab_tmp(exist_flag) SELECT count(1) FROM ' + @table_name + ' WHERE ' + @field_name + ' LIKE ''Copy (' + CONVERT(VARCHAR(4), @i) + ') of ' + @tmp_field_value + ''''
  EXEC sp_executesql @sql

  select @exists = exist_flag from ##tab_tmp
  IF (@exists = 0)
  BEGIN
   SET @new_object_name = 'Copy (' + CONVERT(VARCHAR(4), @i) + ') of ' + @tmp_field_value
   RETURN 0
  END
  SET @i = @i + 1
 END
 
 -- if error occur return null
 RETURN 1
 
END

 

[@more@]