How to pass string array in SQL parameter to IN clause in SQL

Question

0.00/5 (No votes)

See more:

Hi Geeks,

A Logic which I am doing in a complex way. I just need to execute this query in Stored Procedure

SQL

select Sizes,SUM(Quantity) from tbl_SizeBreakup where (Brand=@brand) and (Combo in ('1','2')) ...

The combo I must pass in SQL PARAMETER using in C# is

C#

DataSet dt = new DataSet();
    cmd = new SqlCommand();
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.CommandText = "sp_Accessories";
    cmd.Connection = con;

    cmd.Parameters.AddRange(
            new SqlParameter[] {
                new SqlParameter("@Mode",mode),
                new SqlParameter("@Combo",combo),
        }}

So If I pass 1 parameter, working as expected. The combo which I should pass is a string[](string array).The array length can be anything depends on the user selecting in UI.

My question is how to pass string[] to

C#

new SqlParameter("@Combo",combo)

Posted 21-Jul-14 18:41pm

Sriram Ramachandran

Add a Solution

3 solutions

Solution 1

If you mean SQL Server, pass it as a Table-valued parameter:
SQL Server 2008 User Defined Table Types and Table-Valued Parameters[^]

Posted 21-Jul-14 19:16pm

PIEBALDconsult

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-07-21T19:37:00

Solution 3

You should pass combo (whatever it means) as: '1,2,3,..., 21,...N' instead of string array parameter. Than you'll be able to split values on comma. See: Using comma separated value parameter strings in SQL IN clauses[^]

Posted 21-Jul-14 19:37pm

Maciej Los

Comments

Sriram Ramachandran 22-Jul-14 1:47am

cmd.Parameters.AddWithValue("@color", parameters); The value of parameters would be '1','2' and this pass to SQL SP to @color.
select Sizes,SUM(Quantity) as Quantity from tbl_SizeBreakup where (Combo_Color in (@color)) Group By Sizes ...
So will this work??

Maciej Los 22-Jul-14 1:51am

No! Please read my answer carefully and follow the link.

Sriram Ramachandran 22-Jul-14 2:01am

Understood and worked ... Thanks....

Maciej Los 22-Jul-14 2:04am

You're very welcome. Please, accept my answer as a solution (green button) to remove question from unanswered list.

Ayan Chalki · Accepted Answer · 2014-07-21T19:23:00

Hi Ramachandran,

You can just send the @Combo value parameter as "1,2,hj,lo,2".
and in sql stored procedure split these values with "," (Comma) and create a table with these values. after that you can write your query like
select Sizes,SUM(Quantity) from tbl_SizeBreakup where (Brand=@brand) and (Combo in (select value from @Values)) ...

here is a sample code what you have to do in stored procedure(suppose you have pass
new SqlParameter("@Combo","1,2,lo,54"))

SQL

declare @Values Table
(
RowId int Not Null Identity(1,1) Primary Key
,Value nvarchar(255) Not Null
) 
set @Delimiter=','

Declare @startPos smallint
,@endPos smallint

If (Right(@Combo, 1) != @Delimiter)
Set @Combo = @Combo + @Delimiter
Set @startPos = 1
Set @endPos = CharIndex(@Delimiter, @Combo)

While @endPos > 0
Begin
Insert @Values(Value)
Select LTrim(RTrim(SubString(@Combo, @startPos, @endPos - @startPos)))

-- remove the delimiter just used
Set @Combo = Stuff(@Combo, @endPos, 1, '')

-- move string pointer to next delimiter
Set @startPos = @endPos

Set @endPos = CharIndex(@Delimiter, @Combo) 
end

select Sizes,SUM(Quantity) from tbl_SizeBreakup where (Brand=@brand) and (Combo in (select Value from @Values))

I think you will get what you needed..