Nested Scalar Query Problem

I'm having a trouble with a nested scalar query

I have a table that refers to itself, and I need to loop through it to get the total number of entries in the structure with an object_type of 40 this is the function I came up with to do this. The problem is that the nested function doesn't seem to get called.




Code Snippet
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections.Generic;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
private struct PerformanceCriteriaCounter
{
public PerformanceCriteriaCounter(int id, string objecttype)
{
Id = id;
Object_Type = objecttype;
}
public int Id;
public string Object_Type;
}

[Microsoft.SqlServer.Server.SqlProcedure(Name = "usp_PerformanceCriteriaCounter")]
public static int CountPerformanceCriteria(int ParentID, int SourcePosition)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
int Counter = 0;
int Counter2 = 0;
SqlContext.Pipe.Send("Called with ParentID=" + ParentID + " SourcePosition=" + SourcePosition);
conn.Open();
using (SqlCommand myCommand = conn.CreateCommand())
{
myCommand.CommandText = @"SELECT CompetenceLearningObject.Object_Type, CompetenceLearningObject.LearningObject_ID
FROM CompetenceLearningObjectParent INNER JOIN
CompetenceLearningObject ON CompetenceLearningObjectParent.LearningObject_ID = CompetenceLearningObject.LearningObject_ID
WHERE (CompetenceLearningObjectParent.Approved = 1) AND (CompetenceLearningObject.Archived = 0 OR
CompetenceLearningObject.Archived IS NULL) AND (CompetenceLearningObject.Approved_Status = 1) AND
(CompetenceLearningObjectParent.Parent_ID = @ParentID) AND (CompetenceLearningObjectParent.Position_ID = @SourcePosition)";
myCommand.Parameters.AddWithValue("@ParentID", ParentID);
myCommand.Parameters.AddWithValue("@SourcePosition", SourcePosition);
List<PerformanceCriteriaCounter> loList = new List<PerformanceCriteriaCounter>();
SqlDataReader reader = myCommand.ExecuteReader();
while (reader.Read())
{
SqlContext.Pipe.Send("Adding item");
loList.Add(new PerformanceCriteriaCounter(int.Parse(reader["LearningObject_ID"].ToString()), reader["Object_Type"].ToString()));
}
reader.Close();

foreach (PerformanceCriteriaCounter lo in loList)
{
SqlContext.Pipe.Send("Testing item, ObjectType =" + lo.Object_Type);
if (lo.Object_Type == "40")
{
SqlContext.Pipe.Send("Incrementing counter");
Counter++;
}
else
{
SqlContext.Pipe.Send("Calling procedure with ParentID=" + lo.Id + " and Position=" + SourcePosition);
using (SqlCommand myCommand3 = conn.CreateCommand())
{
myCommand3.CommandType = CommandType.StoredProcedure;
myCommand3.CommandText = "usp_PerformanceCriteriaCounter";
myCommand3.Parameters.AddWithValue("@ParentID", lo.Id);
myCommand3.Parameters.AddWithValue("@SourcePosition", SourcePosition);
object objValue = myCommand3.ExecuteScalar();
if (objValue != DBNull.Value)
{
Counter2 = Convert.ToInt32(objValue);
SqlContext.Pipe.Send("It's not null value=" + Counter2.ToString());
Counter = Counter + Counter2;
}
}
}
}
}
return Counter;
}
}
}






This is the output from SQL Server when I call the function.



Code Snippet
Called with ParentID=1 SourcePosition=89
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Testing item, ObjectType =30
Calling procedure with ParentID=2 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=3 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=4 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=5 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=6 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=7 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=8 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=9 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=10 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=11 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=12 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=13 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=14 and Position=89
It's not null value=0





You can see it adding and checking the values and attempting to call itself, but it never seems to get re-called, any ideas?

Cheers

Jon