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