存储过程中的数组参数处理

• 下载源代码 - 1.5 KB

引言

本文解释了如何将数组传递到存储过程中。我提供了功能完整的源代码，自解释性强。

背景

在阅读了这篇 CodeProject 文章 后，我尝试使用 Image 数据类型，但在我的项目中实施时遇到很多问题。所以我再次尝试使用字符串，但它只能接受单维数组。 技巧是将值列表转换为用逗号分隔的字符串，然后将字符串传递到存储过程中，在那里我们将字符串转换为表，然后遍历该表以获取列表值。

Using the Code

此示例解释了如何在 SQL Server 中处理数组。为此，我编写了一个名为“Sample_Array_Handling”的存储过程和一个名为“Split”的函数。存储过程接受一个字符串参数 [用逗号分隔的数组项]，并打印所有数组项。

步骤

1. 将数组作为字符串传递，每个数组项用“,”分隔。
2. 使用“Split”函数分割字符串。
3. 创建一个临时表，并将步骤 2 的结果集插入到表中。
4. 最后，使用游标遍历表行并获取每个数组项。

示例：Sample_Array_Handling '1,2,3'

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:        Srinath
-- Create date:     May 22 2008
-- Description:    Array Handling Sample
-- =============================================
CREATE  PROCEDURE [dbo].[Sample_Array_Handling]
   @SampleArray nvarchar(10)
AS

BEGIN

Create table #tempArrayTable (rid varchar(500))
Insert into # tempArrayTable (rid)
(select value from dbo.Split(@SampleArray,','))

DECLARE @ArrayItem nvarchar(100)
DECLARE @Array_Cursor CURSOR
SET @Array_Cursor = CURSOR FAST_FORWARD FOR select rid 
from # tempArrayTable

OPEN @ Array_Cursor

FETCH NEXT FROM @ Array_Cursor  INTO @ArrayItem


WHILE @@FETCH_STATUS = 0  
BEGIN

print  @ArrayItem

FETCH NEXT FROM @ Array_Cursor INTO @ArrayItem

END

Close  Array_Cursor
deallocate  Array_Cursor

END

代码

-- =============================================
-- To Split the string and returns a table
-- =============================================
CREATE  FUNCTION [dbo].[Split](@sText varchar(8000), @sDelim varchar(20) = '
')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx int,
@value varchar(8000),
@bcontinue bit,
@iStrike int,
@iDelimlength int

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

if(Len(@sText) = 0)
return

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN

--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.

IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END

--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

END
ELSE
BEGIN
--If you can't find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0
END
END

END

RETURN
END

关注点

您可能遇到过想要拆分字符串或从值列表中创建表的情况。在这种情况下，您可以使用我随本文附带的 Split 函数。

在我的解决方案中，我使用游标从表中获取值。您可以避免使用游标；有关更多信息，请参阅 这篇文章。