How to pass list of values(us,canada,japan) as parameters to SP and get output(10,20,40) as list as well

Question

Post reply

How to pass list of values(us,canada,japan) as parameters to SP and get output(10,20,40) as list as well

sqldoubt

SSC-Addicted

Points: 422
More actions
January 10, 2011 at 8:57 am

#243454

I need to pass list of values to SP and get output as an comma separated list and insert the output values to a table. Am Using 2005 SQL Server
a c# List <> object holds list of values. I need to Pass this list to a stored procedure.
Inside the SP i need to query a table and the Out of SP should be corresponding country id's in this pattern 2,1,7...etc
Now I am doing it this way by looping through the list, adding commas and inserting to a table. but for security and efficiency im asked to do it using SP. Any help is appreciated.
protected string GetCountryIds(List<string> lstCountries)
{
string strCountryIds = string.Empty;
foreach (string strCountry in lstCountries)
{
using (SqlConnection connection = new SqlConnection(connectionInfo))
{
SqlCommand cmd = new SqlCommand(string.Format("{0}{1}{2}", "select * from tblCountries where Country='", strCountry, "'"), connection);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
adp.Fill(dt);
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
if (dr["CountryId"] != null)
{
strCountryCodes += string.Format("{0}{1}",dr["CountryId"],",");
}
}
}
}
}
strCountryCodes = strCountryCodes.TrimEnd(',');
return strCountryCodes;
protected void PopulateMainTable(List<Video> allVideos)
{
case "Countries":
List<string> lstCountries = (List<string>)propValue;
listValues = GetCountryIds(lstCountries);
break;
case "Categories":
List<string> lstCategories = (List<string>)propValue;
listValues = GetCategoryIds(lstCategories);
break;
BulkCopy(dt, "tbldata");}
[\code]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

This might help: http://www.sqlservercentral.com/articles/Array/70702/

Depending on how you structure things, you need to parse your procedure.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Steve Jones - SSC Editor (1/10/2011)
This might help: http://www.sqlservercentral.com/articles/Array/70702/
Depending on how you structure things, you need to parse your procedure.

Heh... No! 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

sqldoubt (1/10/2011)
... and get output as an comma separated list and insert the output values to a table.

I have to tell you that's a very bad thing to do in most cases. With exceptions so rare that I can't even think of one right now, storing CSV data in a table goes against the idea of normalized data and a whole bunch of other things that will put you in a "world of hurt" T-SQL wise.

That, not withstanding, here are a couple of articles that will help you in your parsing endeavors...

http://www.sqlservercentral.com/articles/T-SQL/63003/

http://www.sqlservercentral.com/articles/T-SQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)