Selecting Data From A Temp Table In A SP -- Very Urgent

Hello. I have a SP. I am passing two variables to it that are comma seperated lists (ids) so that I can use an IN clause. But since I cannot use these CSV in an IN clause directly I wrote a temp table that first parses the CS list and creates a temp table with a single column of IDS.

Then in a second temp table I am selecting the data using
enrollment_id IN (Select enrollment_id From #temptable)

Then the SP selects data from the second temp table.

This works exactly the way it is supposed to work. But when I call the SP from ASP.NET it doesn't return any data. I just cant seem to return data by selecting from a temp table. And my ASP.NET code is correct because I commented out everything and just returned the parameters I was passing and that worked.

So does anyone know how to select data from a temp table in a SP when executed from ASP.NET?

I even tried Table Variables and same thing. It works great in SQL but when called from ASP.NET it doesnt return any data. I am guessing it's probably losing its scope.

Any quick help would be appreciated. Thank you.

Here is a snippet of the SP (even this fails):

CREATE PROCEDURE [dbo].[returnEnrollments](@organization_id int, @test_item_list ntext, @enrollment_list ntext)

As

--select  @test_item_list as tilist, @enrollment_list as elist

Declare @report_start_date datetime;
Declare @report_end_date datetime;
Declare @test_item_id_list varchar(8000);
Declare @enrollment_id_list varchar(8000);
DECLARE @TestItemID varchar(10), @Pos int
DECLARE @EnrollmentID varchar(10)

--Set @IDList = @test_item_id_list;
--Set @EnrollmentIDList = @enrollment_id_list;

Set @test_item_id_list = Cast(@test_item_list As varchar(8000))
Set @enrollment_id_list = Cast(@enrollment_list As varchar(8000))

Select @report_start_date = report_start_date, @report_end_date = report_end_date
From organization Where organization_id = @organization_id;

-- Create a temp table to store all the enrollment ids
IF OBJECT_ID('tempdb..#EnrollTemp') IS NOT NULL
DROP TABLE #EnrollTemp

CREATE TABLE #EnrollTemp (enrollment_id int NOT NULL)

SET @enrollment_id_list = LTRIM(RTRIM(@enrollment_id_list))+ ','
SET @Pos = CHARINDEX(',', @enrollment_id_list, 1)

IF REPLACE(@enrollment_id_list, ',', '') <> ''
BEGIN
    WHILE @Pos > 0
    BEGIN
        SET @EnrollmentID = LTRIM(RTRIM(LEFT(@enrollment_id_list, @Pos - 1)))
        IF @EnrollmentID <> '' And @EnrollmentID > 0
        BEGIN
            INSERT INTO #EnrollTemp (enrollment_id) VALUES (CAST(@EnrollmentID AS int)) --Use Appropriate conversion
        END
        SET @enrollment_id_list = RIGHT(@enrollment_id_list, LEN(@enrollment_id_list) - @Pos)
        SET @Pos = CHARINDEX(',', @enrollment_id_list, 1)
    END
END

Select top 20 * From #EnrollTemp
 
 
Edit: Just used a DataReader instead of a datatable and it works. Dont know why it would fail with a datatable.