只向sql表插入随机id的第一个字符。

Question

我正在做一个大容量的插入到一个表。我有多个触发器可以将几个列从大容量插入插入到不同的表中。我生成长度为25的字母数字字符作为id。为此，我创建了一个函数，调用这个函数，在这里我需要@ID列的值。但是我只得到了插入的随机字符串中的第一个数字。

在我调用函数的地方触发

 IF NOT EXISTS(SELECT * FROM CHEMICAL WHERE Chemical_Name = @Chemical_Name)
    BEGIN
        INSERT INTO CHEMICAL(Chemical_ID, Chemical_Name, CAS_Number,EHS,TPQ_Pounds,RQ_Pounds,last_updated)
        VALUES(dbo.SDS_GENERATE_ID(), @Chemical_Name,@CAS_Number, @EHS,@TPQ_Pounds,@RQ_Pounds,getdate())
    END 

函数SDS_GENERATE_ID

CREATE FUNCTION SDS_GENERATE_ID()
RETURNS VARCHAR
AS
BEGIN

DECLARE @r varchar(25)
SELECT @r = coalesce(@r, '') + n
FROM (SELECT top 25 
CHAR(number) n FROM
master..spt_values
WHERE type = 'P' AND 
(number between ascii(0) and ascii(9)
or number between ascii('A') and ascii('Z')
or number between ascii('a') and ascii('z'))
ORDER BY (select * from MyRAND)) a
return @r
   END;
   GO

MyRand视图，因为我不能在udfs中使用NEWID()

CREATE VIEW [dbo].[MyRAND]
AS
select newid() as randID
GO

​

​

任何帮助都要事先表示感谢和感谢。

Answer 1

CREATE FUNCTION SDS_GENERATE_ID()
RETURNS VARCHAR

您将需要指定长度，否则最终将收到单个字符。

当前形式的函数只返回值以下的值。

0

当通过指定长度来修改时，如下所示:返回

CREATE FUNCTION SDS_GENERATE_ID()
RETURNS VARCHAR(25)

给出

01234656789..

Answer 2

函数返回类型中没有指定的字符长度：

alter FUNCTION SDS_GENERATE_ID()
RETURNS VARCHAR(20)
AS
BEGIN

DECLARE @r varchar(25)
SELECT @r = coalesce(@r, '') + n
FROM (SELECT top 25 
CHAR(number) n FROM
master..spt_values
WHERE type = 'P' AND 
(number between ascii(0) and ascii(9)
or number between ascii('A') and ascii('Z')
or number between ascii('a') and ascii('z'))
ORDER BY (select id from employee)) a
return @r
   END;
   GO

注:替换上述返回类型所需的长度