one String with Different Values

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi All,

I have a Store Proc in sql 2005 with the below command:-

select * from test1
where department in (@department)

I have a listbox which have a,b,c and d items.

I user can select any one or more items. I would like to use that in @department.

I have a problems.

1) @department is not taking more then one item. If we select one item its working but more then one will not work.

Thanks in advance !!!

Posted 30-Apr-12 11:29am

Jaggi_singh

Add a Solution

Comments

Jaggi_singh 1-May-12 2:07am

Sorry, i am new to sql and asp.net. so, I am not able to understand what you people try to explain. i can try to explain my problem again.

I have a asp.net aspx page with 3 listbox.

1) Process > (2) Blank > (3) Blank.

I have a table whose name is test1 with two headers

1) process 2) Department

what i want:----

When i select multiple items from process and move to 2nd list box. The 3rd listbox automatically fill with related Departments.

--------------------------------SQL COMMAND----------------------------------------
For that i create a SP

Create a proc dept as

Select * from test1

where Department = (@Department)

------------------------------ASP.NET CODING-------------------------------------

{
int i = 0;
while (i < ListBox1.Items.Count)
{
if (ListBox1.Items[i].Selected)
{
ListBox2.Items.Add(ListBox1.Items[i].Text);
}
i++;

}
// the above code is using for moving multiple items.

for (int J = 0; J < ListBox2.Items.Count; J++)
{

String T = (ListBox2.Items[J].Text.ToString()) + ",";

Response.Write(T);

}

------------------------------------------------------------------

I am passing T value in @Department in SQL but :(

its not working ....

2 solutions

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)

ekolis · Answer 1 · 2012-04-30T11:47:00

I don't think stored procedure parameters can take multiple values. What you could do instead is pass in the list of items as a comma delimited string, and then structure your query like so:

SQL

select * from test1
where
(@department = department) or
(@department like '%,' + department) or
(@department like department + ',%') or
(@department like '%,' + department + ',%')

Alternatively you could write a SQL function to parse the string into a table variable, then join your table with the table variable to find matches.

VJ Reddy · Answer 2 · 2012-04-30T17:48:00

I think the Execute command explained here
http://msdn.microsoft.com/en-us/library/ms188332.aspx[^]
can be used for this purpose by constructing the query string as shown below

SQL

EXECUTE ('select * from test1 where department in ' + @department)
--The @department is passed to the stored procedure as
-- ('a','b','c','d')

one String with Different Values

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0