How to send multiple parameters hyphen separated in to a stored procedure as a List?

Question

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.

You want to send it as a single parameter or separate them in multiple parameters ? — ThePravinDeshmukh, May 08 '15 at 07:25
wanna sent it like Table-Valued Parameters and then use all separately there at procedure. — atul gupta, May 08 '15 at 07:30
Never send variable length arrays using a srting. Always use a table value parameter for that. — Zohar Peled, May 08 '15 at 08:03
possible duplicate of [How to pass string array in SQL parameter to IN clause in SQL](http://stackoverflow.com/questions/24879020/how-to-pass-string-array-in-sql-parameter-to-in-clause-in-sql) — Zohar Peled, May 08 '15 at 10:35
[Check out this answer.](http://stackoverflow.com/a/24881759/3094533) If it helps you, I would appriciate a vote up on it. — Zohar Peled, May 08 '15 at 10:36

ThePravinDeshmukh · Answer 1 · 2015-05-08T07:48:19.047

If you want to send it as single paramter :

Use below function to split string in 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

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 :

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);

Geewers · Answer 2 · 2015-05-08T13:59:11.823

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.

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

How to send multiple parameters hyphen separated in to a stored procedure as a List?

2 Answers2