SQLServer-存储过程中使用字符串和分隔符实现传递数组参数(ntext逗号连接的字符串转换成列)

一 简介

在高级语言中，很容易编写带有数组参数的函数。但在数据库的存储过程中却没有那么容易，因为存储过程的参数只能以一些基本类型作为参数。我们希望数组作为参数的情况是很常见的，例如有一个表Table(Id int, Data nvarchar(50))，需要向该表一次存入一批数据。如果存储过程以基本数据类型作为参数，定义为InsertData(@data nvarchar(50)), 那么需要循环多次调用该存储过程。

要使存储过程支持数组参数，需做一点变通。可以将需输入的数据转换成字符串，并以某一个分隔符隔开组成一个大的字符串，可以用基本类型text 或 ntext表示。将这个字符串传给存储过程，存储过程内部将其解析，即去掉分隔符，把这批数据放入零时表或某个标变量中，最后批量插入数据表中。

二 解析含有分隔符的字符串生成表变量的流程（创建处理过程的Function）

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SplitTextToStringArray]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[SplitTextToStringArray]

GO

-- =============================================

-- Author:      fishinthewind

-- Create date:   7/10/2007

-- Description: Split string variant with type of ntext

-- =============================================

CREATE FUNCTION [dbo].[SplitTextToStringArray]

(

   @text ntext,

   @delimiter char(1)

)

RETURNS @arrayTable TABLE(Idx bigint, [Value] nvarchar(200))

AS

BEGIN

     DECLARE @splitlen int

    SET @splitlen = 4000



    DECLARE @Idx int SET @Idx = 0



    -- 定义取子串的起始位置

    DECLARE @textsplit bigint

    SET @textsplit = 1

    WHILE( @textsplit <= DATALENGTH(@text) )

    BEGIN



        -- 由于许多字符串处理函数无法用于ntext数据类型

        -- 所以需要循环按批处理ntext字符串，一批取出

        -- 个字符放入nvarchar(4000)类型的变量中.

        DECLARE @string nvarchar(4000)

        SELECT @string = SUBSTRING(@text,@textsplit,@splitlen)



        -- 能够取出满个字符

        IF LEN(@string) = @splitlen

        BEGIN

            -- 确保取出的个字符是完整的由分隔符隔开的字符串组合

            DECLARE @lastcomma int

            SELECT @lastcomma = CHARINDEX(@delimiter,REVERSE(@string),1)

            -- 最后一个分隔符后面的字符串不完整，应抛弃

            IF @lastcomma > 0

            BEGIN

                SELECT @string = SUBSTRING(@string,1,@splitlen - @lastcomma)

                -- 设置下一次从@text取字符的起始位置

                SELECT @textsplit = @textsplit + @splitlen - @lastcomma + 1

            END

            -- 最后一个分隔符后面的字符串完整.

            ELSE

            BEGIN

                SELECT @textsplit = @textsplit + @splitlen + 1

            END

        END

        -- 取出不满个字符

        ELSE

        BEGIN

            SELECT @textsplit = @textsplit + @splitlen + 1

        END

      

        -- 解析@string，取出以分隔符为界限的子字符串

        DECLARE @i1 int SET @i1 = 1

        DECLARE @i2 int SET @i2 = 1

        WHILE @i1 <= LEN(@string)

        BEGIN

            SET @i2 = CHARINDEX(@delimiter,@string,@i1+1)

            IF @i2 = 0

                SET @i2 = LEN(@string) + 1

            INSERT @arrayTable (Idx, Value)

            SELECT @Idx, SUBSTRING(@string,@i1,@[email protected])

            SET @i1 = @i2 + 1

            SET @Idx = @Idx + 1

        END

    END

    RETURN

END

GO
           

三 使用该字符串分隔函数的过程脚本 （创建存储过程）

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCategoryRevisionIds]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[GetCategoryRevisionIds]

GO

-- =============================================

-- Author:      fishinthewind

-- Create date:   7/10/2007

-- Description: 使用字符分割函数

-- =============================================

CREATE PROCEDURE [dbo].[GetCategoryRevisionIds]

(

   @stringArray ntext

)

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;



    DECLARE @stringArrayT TABLE(Idx bigint, [Value] nvarchar(200))

    INSERT INTO @stringArrayT(Idx, [Value])

    (

       SELECT Idx, [Value]

       FROM SplitTextToStringArray(@stringArray, ',')

    )



    SELECT * FROM @stringArrayT                  

END

GO
           

 说明：此处使用的是临时表，当然也可以创建真正的表，那样就无需使用存储过程。

测试调用示例：

declare @tt nvarchar(max)
  begin
  SELECT  @tt=DanWeiIDs
  FROM [dbo].[GetImportConfig]
  exec [GetCategoryRevisionIds]  @tt
  end
           

四  对于局部变量，text、ntext 和 image 数据类型无效 的解决办法

sql2005或以上版本支持新数据类型：
varchar(max)
nvarchar(max)

微软建议使用 varchar(max) 来代替 text，使用 nvarchar(max) 来代替 ntext，使用 varbinary(max) 来代替 image。

max长度可达到2G，远远大于8000