How can I fill a table variable within a function with SP data?

[cmgaviao]

I have the following function Below. I was attempting to use TempTables within in it, but apparently temptables aren't supported within functions. So, thinking I was really slick, I decided to replace the temptables with table variables. Only to find out that:


EXECUTE cannot be used as a source when inserting into a table variable.


Anyhow, if anyone has any suggestions, I would greatly appreciate them. Below is my code:


CREATE Function cmtest2-- 2, NULL, NULL, NULL, NULL
(
@UserID int
,@ObjectTypeID int
,@strSqlStatement varchar(4000)
,@strInList varchar(4000)
,@strDelimeter char(1)
)

RETURNS @tblRightsObjects TABLE (ObectID int)

as

Begin

--Create list of ObjectTypeTypes to Get rights for:

Declare @tblInListValues TABLE (ObjectTypeTypeID int)

IF @strSqlStatement IS NOT NULL
BEGIN
INSERT INTO @tblInListValues
EXEC @strSqlStatement
END

IF @strInList IS NOT NULL
BEGIN
INSERT INTO @tblInListValues
SELECT * FROM udfArraySplitter(@strInList, @strDelimeter)
END

IF @ObjectTypeID IS NOT NULL
BEGIN
INSERT INTO @tblInListValues
VALUES(@ObjectTypeID)
END

--Create Group Table if Working with Group Data

DECLARE @tblGroup TABLE(GroupID int, GroupLevel int)
INSERT INTO @tblGroup EXEC spGetUserGroups @UserID, 'N', 'N'

--Move User Objects Into Return Table
Insert Into @tblRightsObjects
select distinct objectID
from tblsecrightassign tSRA
where objecttypeid in (
select distinct objecttypetypeid from @tblInListValues
)
AND RAID = @userID
AND TypeID = 0

--Move Groups Into ObjectTable
INSERT INTO @tblRightsObjects
SELECT ObjectID
FROM tblSecRightAssign
WHERE RAID IN (SELECT DISTINCT GroupID
FROM @tblGroup
)
AND TypeID = 1
AND ObjectTypeID in (
select distinct objecttypetypeid from tbltransaction
)
AND RAID = @userID

--Finish

Return

END

thanks in advance for any assistance.

cmgaviao.

 

[hmckillop]

The exec cannot be inserted into the table variable as it is executed in a different context to where the table variable is declared.
Why dont you create a actual table and use it as a temp table? Do exactly the same as a temp table and performance wont degrade too much.

"I'm living so far beyond my income that we may almost be said to be living apart