Pass (1,2,3,4) as a SqlParameter

Question

I am trying to pass this dynamically generated string (1,2,3,4) but I am getting the error:

Conversion failed when converting the nvarchar value '1, 2, 3, 4' to data type int

How can I fix this error?

    public DataTable GetAll(int [] idArray)
    {
        DataTable dt = new DataTable();

        var paramNames =   Enumerable
                          .Range(0, idArray.Length)
                          .Select(index => $"@prm{index}")
                          .ToArray();
        string query = $"SELECT link FROM Test WHERE id IN ({string.Join(",", paramNames)}) ORDER BY seq.Sequence";

        try
        {
            using (SqlConnection conn = new SqlConnection(connStringUniApp))
            {
                conn.Open();
                using (SqlCommand command = new SqlCommand(query, conn))
                {
                    for (int i = 0; i < paramNames.Length; ++i)
                    {
                        command.Parameters.Add(new SqlParameter(paramNames[i], idArray[i]));
                    }

                    using (SqlDataReader rdr = command.ExecuteReader())
                    {
                        dt.Load(rdr);
                    }
                    command.Parameters.Clear();
                }
                return dt;
                Console.Write(dt);
            }
        }
        catch (Exception ex)
        {
            Console.Write(ex.ToString());
            return dt;
        }
    }

If the values in the database ae integers why are yoiu converting to a string? parameterString.ToString() — jdweng, Dec 28 '16 at 10:12
Because I don't know how many integers I will pass. They are dynamically generaged — , Dec 28 '16 at 10:14
What you are trying will not work. Unfortunately. You will have to build the complete string and (i.e.. query = "SELECT * From test where testId IN (1,2,3,4)", Or on the SQL side use a function like fn_Split to break the string "1,2,3,4" into a list. — Richard Vivian, Dec 28 '16 at 10:14
Try to use Inner sql query inside the IN keywork .by which you can select 1,2,3,4 — Raju Mali, Dec 28 '16 at 10:15
Possible duplicate of [How to pass an array into a SQL Server stored procedure](http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure) — Alejandro, Dec 28 '16 at 10:22
the `IN()` operator expects a comma separated list of arguments, but you have supplied it with a single argument that contains a comma separated list of values. — Zohar Peled, Dec 28 '16 at 10:32

Dmitry Bychenko · Accepted Answer · 2016-12-28T10:42:24.567

0

You have to either generate many parameters:

string[] paramValues = parameterString.Split(',');

var paramNames = Enumerable
  .Range(0, paramValues.Length)
  .Select(index => $"@prm{index}")
  .ToArray();

string query = $"SELECT * from test WHERE testId IN ({string.Join(",", paramNames)})";

...

for (int i = 0; i < paramNames.Length; ++i)
  command.Parameters.Add(new SqlParameter(paramNames[i], paramValues[i]));

Or do not use parameters at all and add the string as is which easier to implement but prone to SQL injection and that's why can't be recommended:

string query = $"SELECT * from test WHERE testId IN ({ParameterString})";

edited Dec 28 '16 at 10:42

answered Dec 28 '16 at 10:16

Dmitry Bychenko

180,369
20
160
215

1

For suggesting many parameters +1. But -1 for suggesting SQL injection as a second chance :D – Alejandro Dec 28 '16 at 10:20
Thank you. I modified the code accordingly to your suggestions but I am getting empty return. The modified code you can see above. – Dec 28 '16 at 11:11
@user6934713: It's time to *debug*. Put a breakpoint on ` using (SqlDataReader rdr = command.ExecuteReader())` line and examine `command.CommandText` as well as `command.Parameters` – Dmitry Bychenko Dec 28 '16 at 11:25
Everything looks OK the value of commandText is SELECT link FROM Test WHERE id IN (@prm0,@prm1,@prm2) ORDER BY id. The parameters are with the right name and value. If I run the query in management studio returns value – Dec 28 '16 at 11:38
@user6934713: do you have any rows returned? Please, inspect `rdr.HasRows` on `dt.Load(rdr);` line – Dmitry Bychenko Dec 28 '16 at 11:48
NO I dont. If I use the query in management studio works fine. – Dec 28 '16 at 11:57

score 0 · Answer 2 · answered Dec 28 '16 at 10:16

0

try this

command.Parameters.Add(new SqlParameter("@ParameterString", Convert.ToInt32(parameterString.ToString())));

answered Dec 28 '16 at 10:16

Genish Parvadia

1,437
3
17
30

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - [From Review](/review/low-quality-posts/14721786) – spydon Dec 28 '16 at 10:35
1

@spydon my review incorrect answer is not incorrect – Genish Parvadia Dec 28 '16 at 10:38
It is not a definite answer, it could just as well go as a comment. – spydon Dec 28 '16 at 10:39

score 0 · Answer 3 · answered Dec 28 '16 at 11:19

You can write a function in SQL server and call the function to split your string parameter and get integer ID to check condition.

string query = "SELECT * from test t join [dbo].[SplitStringByComma](@ParameterString, ',') s on  s.value = testId ORDER BY Id"

your function to split string by comma goes like this:

ALTER FUNCTION [dbo].[SplitStringByComma]  (    
@SplitString varchar(max),
@char char)
RETURNS @Values TABLE 
(
 value int
)
AS

begin
declare @CharPosition int
declare @SubString varchar(max)
set @SplitString = RTRIM(LTRIM(@SplitString))   
  if(len(@SplitString)>0 and CHARINDEX(@char,@SplitString)>0)
  begin
    while(CHARINDEX(@char,@SplitString)>0)
        begin
        set @CharPosition = CHARINDEX(@char,@SplitString)
        set @SubString= SUBSTRING(@SplitString,0,@CharPosition)
        if(len(@SubString)>0)
        insert into @Values
        select @SubString
        set @SplitString = SUBSTRING(@SplitString,@CharPosition+1,LEN(@SplitString))        
        end
        if(len(@SubString)>0)
        insert into @Values select @SplitString
   end
   else
        insert into @Values select @SplitString
return
end

Pass (1,2,3,4) as a SqlParameter

3 Answers3