This project is read-only.
1
Vote

Could not load data from function(Sql server 2008)

description

Hi All.
 
 
 
I have a function below
 
 
CREATE FUNCTION [dbo].[GetSchool]
(
@parentID UNIQUEIDENTIFIER  
)
RETURNS @tbl TABLE (id int,value UNIQUEIDENTIFIER)
WITH EXECUTE AS CALLER
AS
begin
INSERT @tbl
        ( id, value )

( 
SELECT * FROM .[dbo].[udfNodeSchool] (
@parentID    
))
 insert @tbl(id,value) VALUES( 0,@parentID)
return
end
 
my function udfNodeSchool
 
 


CREATE FUNCTION [dbo].[udfNodeSchool]
(
@parentID UNIQUEIDENTIFIER
)
RETURNS @tbl TABLE (id int,value UNIQUEIDENTIFIER)
WITH EXECUTE AS CALLER
AS
begin

if (@parentID is NOT NULL)
begin
declare @tempCount int
declare @count int
set @count = 0
select @count = count(ID) from [dbo].[t_Schools] where ParentID = @parentID
if(@count > 0)
begin
insert @tbl(id,value) select ROW_NUMBER() over( order by ID),ID
from [dbo].[t_Schools]
where ParentID= @parentID
declare @row int
set @row = 1
while @row <= @count
begin  
 select @tempCount = count(id) from @tbl  
 select @parentID = value from @tbl where id = @row  
 insert @tbl(id,value)   
 select ROW_NUMBER() over( order by ID) + @tempCount, value  
 from dbo.udfNodeSchool(@parentID)  
 set @row = @row +1  
end  
end
end
return
end

and i get from all student in school by code
 
ObjectSet<t_Student> students =ObjectContext.GetObjectSet<t_Student>("SchoolID in (SELECT VALUE from dbo.GetSchool('" + ID+ "'))");
 
then error when i get data. Not return value.

comments