asp.net stored procedure,multiple values in a single parameter

Question

Im fairly new to Asp.Net arena here guys,so please bear with me if say something that's not logical.

I have a procedure that binds to a grid-view that displays data based on the selection made from a drop-down box. It works fine when a single value is sent but I'm thinking of doing multiple selection from the drop-down[using checkboxes] or even using a list-view, and I'm confused how to go about it. This is the code for the stored procedure. At the moment it accepts only a single value. For e.g., if I select Africa from the drop-down it displays all the cities from Africa. But now I would like to have multiple selections for the drop-down so that the grid display cities from more than one country.

CREATE PROCEDURE sp_getCities 
(
@p_country  nvarchar(50)
)
AS

SELECT country, cities
FROM world WHERE country=@p_country

I'm sure there are people who must have come across situations like this. If you have could you please provide me with a link to a tutorial or a solution?

LukeH · Accepted Answer · 2011-04-28 11:54:04Z

2

Here's the (almost canonical) answer:

Arrays and Lists in SQL Server

answered Apr 28, 2011 at 11:54

LukeH

271k59 gold badges373 silver badges411 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

William Xifaras · Accepted Answer · 2011-04-28 11:53:22Z

0

One way is to pass XML into a stored procedure and using the system stored procedure sp_xml_preparedocument. Essentially, you pass XML into the stored procedure and operate on it in a SET oriented manner.

Check out this link to get you started with this approach.

http://msdn.microsoft.com/en-us/library/ms187367.aspx

answered Apr 28, 2011 at 11:53

William Xifaras

5,3622 gold badges22 silver badges22 bronze badges

Collectives™ on Stack Overflow

asp.net stored procedure,multiple values in a single parameter

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related