如何将多个参数连字符分隔为存储过程作为List ？

I have received multiple values hyphen saparated from a text box eg. "one-two-three-four", first i want to store all hyphen saparated values and then send the list to a stored procedure as parameters.

我收到了从文本框中删除的多个值连字符,例如。 “one-two-three-four”,首先我要存储所有连字符saparated值,然后将列表作为参数发送到存储过程。

2 个解决方案

#1

If you want to send it as single paramter :

如果您想将其作为单个参数发送:

Use below function to split string in DB

使用以下函数在DB中拆分字符串

CREATE FUNCTION SplitString
(    
  @Input NVARCHAR(MAX),
  @Character CHAR(1)
)
RETURNS @Output TABLE (
  Item NVARCHAR(1000)
)
AS
BEGIN
  DECLARE @StartIndex INT, @EndIndex INT

  SET @StartIndex = 1
  IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
  BEGIN
        SET @Input = @Input + @Character
  END

  WHILE CHARINDEX(@Character, @Input) > 0
  BEGIN
        SET @EndIndex = CHARINDEX(@Character, @Input)

        INSERT INTO @Output(Item)
        SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

        SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
  END

  RETURN
END
GO

Use it like this in your SP

在你的SP中使用它

SELECT Item
FROM dbo.SplitString('one-two-three-four', '-')

EDIT :

If you want to send it as table-valued parameter :

如果要将其作为表值参数发送:

In C#:

string [] list = Textbox.Text.Split('-');
DataTable dt = new DataTable();
dt.Columns.Add("Column1");
foeach(string s in list)
{
dt.Rows.Add(s);
}

Pass it to SP :

将它传递给SP:

SqlParameter parameter = new SqlParameter(); 
//The parameter for the SP must be of SqlDbType.Structured 
parameter.ParameterName="@Sample"; 
parameter.SqlDbType = System.Data.SqlDbType.Structured; 
parameter.Value = dt; 
command.Parameters.Add(parameter);

#2

Send your delimited set of values to your Stored Procedure as a single Varchar or Nvarchar parameter. Then simply break them apart programatically in your Stored Procedure either in a code block or wrapped in a table-valued function. I prefer the function approach, code reusability is always the better approach. Just be aware of possible SQL injection so apply proper validation on your parameter.

将分隔的值集作为单个Varchar或Nvarchar参数发送到存储过程。然后,只需在代码块中或在表值函数中包装,就可以在存储过程中以编程方式将它们分开。我更喜欢函数方法,代码可重用性始终是更好的方法。请注意可能的SQL注入,因此请对您的参数进行适当的验证。

CREATE FUNCTION [dbo].[fn_DelimitSplitter]
(
        @Delimiter varchar(8) = '',
        @TextToSplit varchar(max)
)
RETURNS @Array TABLE  
(
    Value varchar(64)
) 
AS
BEGIN

    DECLARE @imed varchar(64)

    SET @TextToSplit = @TextToSplit + @Delimiter
    WHILE (PATINDEX('%'+@Delimiter+'%',@TextToSplit) > 0)
    BEGIN
            SET @imed = SUBSTRING(@TextToSplit,0,PATINDEX('%'+@Delimiter+'%',@TextToSplit)+1)
            SET @TextToSplit = SUBSTRING(@TextToSplit,LEN(@imed)+1,LEN(@TextToSplit) + 1)       
            INSERT INTO @Array
            SELECT LTRIM(RTRIM(REPLACE(@imed, @Delimiter,'')))          
    END
    RETURN 
END

Hope this helps

希望这可以帮助

#1