C# and SQL - sub select from a table parameter

Question

Below is a code snippet for passing a table as a parameter to a query that can be used in Sql Server 2008. I'm confused though about the "SELECT id.custid FROM @custids id". Why does it use id.custid and @custids id...?

private static void datatable_example() 
{

   string [] custids = {"ALFKI", "BONAP", "CACTU", "FRANK"};

   DataTable custid_list = new DataTable();
   custid_list.Columns.Add("custid", typeof(String));

   foreach (string custid in custids) {
      DataRow dr = custid_list.NewRow();
      dr["custid"] = custid;
      custid_list.Rows.Add(dr);
   }

   using(SqlConnection cn = setup_connection())
   {
      using(SqlCommand cmd = cn.CreateCommand()) 
      {

         cmd.CommandText =
           @"SELECT C.CustomerID, C.CompanyName
             FROM   Northwind.dbo.Customers C
             WHERE  C.CustomerID IN (SELECT id.custid FROM @custids id)";
         cmd.CommandType = CommandType.Text;

         cmd.Parameters.Add("@custids", SqlDbType.Structured);
         cmd.Parameters["@custids"].Direction = ParameterDirection.Input;
         cmd.Parameters["@custids"].TypeName = "custid_list_tbltype";
         cmd.Parameters["@custids"].Value = custid_list;

         using (SqlDataAdapter da = new SqlDataAdapter(cmd))
         using (DataSet        ds = new DataSet()) {
            da.Fill(ds);
            PrintDataSet(ds);
         }
      }
   }

Brimstedt · Accepted Answer · 2010-06-17 03:20:58Z

3

'id' in the subquery is just an alias.

The same way you have 'C' as an alias in the main FROM clause and use 'C.CustomerID'.

The subquery could just as well have been

 SELECT custid
 FROM @custids

answered Jun 17, 2010 at 3:20

Brimstedt

3,14826 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

C# and SQL - sub select from a table parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related