【IT168技术文档】

　　当在表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' --> 'Copy of ProductReport' --> 'Copy (2) of ProductReport' --> 'Copy (3) of ProductReport'... --> '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