Is there a way to turn this query to regular inline SQL

Question

I would like to turn this query to regular inline sql without using stored procedures

declare @nod hierarchyid
select @nod = DepartmentHierarchyNode
from Organisation
where DepartmentHierarchyNode = 0x6BDA

select * 
from Organisation
where @nod.IsDescendantOf(DepartmentHierarchyNode) = 1

Is there a way to do it?

Answer 1

Sure, no problem at all....

using(SqlConnection con = new SqlConnection(......))
{
   string stmt = 
      "declare @nod hierarchyid; " + 
      "select @nod = DepartmentHierarchyNode from Organisation where DepartmentHierarchyNode = 0x6BDA; " + 
      "select * from Organisation where @nod.IsDescendantOf(DepartmentHierarchyNode) = 1";

   using(SqlCommand cmd = new SqlCommand(stmt, con))
   {
      con.Open();
      using(SqlDataReader rdr = cmd.ExecuteReader())
      {
         // here, read your values back
      }
      con.Close();
   }
}

That should do it. I don't see any trouble having multiple statements in your inline SQL query, really.