Return values from a stored procedure within a stored procedure

I have a stored procedure that generates a list of values according to inheritance.  The inheritance is such that a category belongs to a category which belongs to a category and so on.  At any level I can assign permissions to a category where all children categories then inherit those permissions.  Categories can have multiple groups and or users assigned as permissions (where a group is a collection of users).  No problem, this all works.

I have another stored procedure that finds documents (a table of document details really) where each document belongs to one or more categories.  In this stored procedure I pass a UserId.  What I need to achieve is only returning documents the user is allowed to view.

A document can belong to multiple categories.  A category may or may not have permissions assigned.  If no permissions are assigned any user can view the document.  If multiple categories are assigned and one category has no permissions then the document can be viewed or if the user is allowed to view one category but not another then the document can still be viewed.  If permissions are assigned or inherited then the UserId must be checked against the list of users assigned to the category (either directly or through inheritance) and checked against whether they belong in a group that has been assigned either directly or through inheritance.  It is entirely possible only users or only groups or both have been assigned permissions to a category.

My inheritance check stored procedures are:
ALTER PROCEDURE [dbo].[GetDocumentLibraryCategoryPermissionsTree]
      @SiteId int
AS
WITH PermissionsCTE(ParentCategoryId,CategoryId,GroupNames,GroupIds)
      AS
      (            
            SELECT      X.ParentCategoryId ,
                        X.DocumentLibraryCategoryId ,
                        CASE dbo.GetDocumentLibraryCategoryPermissionNames(X.DocumentLibraryCategoryId )
                               WHEN CAST('' AS NVARCHAR(1000))
                                    THEN CAST('Public' AS NVARCHAR(1000))
                                    ELSE dbo.GetDocumentLibraryCategoryPermissionNames(X.DocumentLibraryCategoryId )
                        END,
                        dbo.GetDocumentLibraryCategoryPermissionIds(X.DocumentLibraryCategoryId,CAST('' AS NVARCHAR(1000)))
            FROM DocumentLibraryCategory X WHERE X.ParentCategoryId=0 AND X.SiteId = @SiteId
            UNION ALL
            SELECT  C.ParentCategoryId ,
                        C.DocumentLibraryCategoryId,
                        CASE dbo.GetDocumentLibraryCategoryPermissionNames(C.DocumentLibraryCategoryId )
                               WHEN CAST('' AS NVARCHAR(1000))
                                    THEN CAST('Inherit' AS NVARCHAR(1000))
                                    ELSE dbo.GetDocumentLibraryCategoryPermissionNames(C.DocumentLibraryCategoryId )
                        END,
                        dbo.GetDocumentLibraryCategoryPermissionIds(C.DocumentLibraryCategoryId,P.GroupIds)
            FROM DocumentLibraryCategory C INNER JOIN
            PermissionsCTE P ON P.CategoryId=C.ParentCategoryId AND C.SiteId = @SiteId
      )
SELECT CategoryId,GroupNames,GroupIds FROM PermissionsCTE

ALTER PROCEDURE [dbo].[GetDocumentLibraryCategoryPermissionsTreeUser]
      @SiteId int
AS
WITH PermissionsCTE(ParentCategoryId,CategoryId,UserNames,UserIds)
      AS
      (            
            SELECT      X.ParentCategoryId ,
                        X.DocumentLibraryCategoryId ,
                        CASE dbo.GetDocumentLibraryCategoryPermissionNamesUser(X.DocumentLibraryCategoryId )
                               WHEN CAST('' AS NVARCHAR(1000))
                                    THEN CAST('Public' AS NVARCHAR(1000))
                                    ELSE dbo.GetDocumentLibraryCategoryPermissionNamesUser(X.DocumentLibraryCategoryId )
                        END,
                        dbo.GetDocumentLibraryCategoryPermissionUserIds(X.DocumentLibraryCategoryId,CAST('' AS NVARCHAR(1000)))
            FROM DocumentLibraryCategory X WHERE X.ParentCategoryId=0 AND X.SiteId = @SiteId
            UNION ALL
            SELECT  C.ParentCategoryId ,
                        C.DocumentLibraryCategoryId,
                        CASE dbo.GetDocumentLibraryCategoryPermissionNamesUser(C.DocumentLibraryCategoryId )
                               WHEN CAST('' AS NVARCHAR(1000))
                                    THEN CAST('Inherit' AS NVARCHAR(1000))
                                    ELSE dbo.GetDocumentLibraryCategoryPermissionNamesUser(C.DocumentLibraryCategoryId )
                        END,
                        dbo.GetDocumentLibraryCategoryPermissionUserIds(C.DocumentLibraryCategoryId,P.UserIds)
            FROM DocumentLibraryCategory C INNER JOIN
            PermissionsCTE P ON P.CategoryId=C.ParentCategoryId AND C.SiteId = @SiteId
      )
SELECT CategoryId,UserNames,UserIds FROM PermissionsCTE


I'm not entirely sure how this works but lets go with my ignorant version.  I should be able to do something like:
SELECT GroupIds FROM ([dbo].[GetDocumentLibraryCategoryPermissionsTree] @SiteId=1) WHERE CategoryId = 5

I'm not even sure if that is accurate.  However, the thing that makes it even more difficult is the @SiteId parameter that needs to be passed is also called @SiteId in the originating stored procedure.

To try to clarify more my document table and category table are associated through another table which holds just the DocumentId and CategoryId (allowing for a many to many relationship kind of scenario).  My select starts like so:
      SELECT DISTINCT DD.DocumentLibraryDocumentDetailsId AS DocID
            , (SELECT TOP 1 DLC.[CategoryName]
                  FROM DocumentLibraryCategory DLC
                  INNER JOIN DocumentLibraryDocumentCategory DocCat ON DocCat.DocumentLibraryCategoryId = DLC.DocumentLibraryCategoryId
                  WHERE DocCat.DocumentLibraryDocumentDetailsId = DD.DocumentLibraryDocumentDetailsId) AS CatName
            , (SELECT TOP 1 DLC.[Position]
                  FROM DocumentLibraryCategory DLC
                  INNER JOIN DocumentLibraryDocumentCategory DocCat ON DocCat.DocumentLibraryCategoryId = DLC.DocumentLibraryCategoryId
                  WHERE DocCat.DocumentLibraryDocumentDetailsId = DD.DocumentLibraryDocumentDetailsId) AS CatPosition
            , DD.Author AS Author
            , DD.FileDate AS Created
            , DD.Title AS Title
            , DD.Code AS Code

I need some sort of WHERE clause that checked the @UserId was "IN #tmpUsers" where #tmpUsers was generated against all categories the Document is assigned.  I don't even know how to begin with getting such a WHERE clause into my stored procedure.  Please help!
LVL 10
dij8Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

maradamCommented:
Rewrite GetDocumentLibraryCategoryPermissionsTree as TableValued Function you will be able to issue the following query:
select * from GetDocumentLibraryCategoryPermissionsTree(1) WHERE CategoryId = 5
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior DBACommented:
Hi

You can do this:
create table #results(
  field1 type
  , field2 type
  , etc
)

insert #results
exec myProcedure Param1, Param2, Param3 etc ...

Cheers
  David
0
dij8Author Commented:
What's a TableValued Function and how do I write one?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

David ToddSenior DBACommented:
Hi,

In SSMS look at the templates - under functions there are two for table-values

http://msdn2.microsoft.com/en-us/library/ms186755.aspx

HTH
  David
0
maradamCommented:
Here is, how to make GetDocumentLibraryCategoryPermissionsTree a table valued function.
create function [dbo].[tfn_GetDocumentLibraryCategoryPermissionsTree]( 
      @SiteId int
)
returns @t table (CategoryId int, GroupNames varchar(1000), GroupIds varchar(1000))
AS
begin
;WITH PermissionsCTE(ParentCategoryId,CategoryId,GroupNames,GroupIds)
      AS
      (            
            SELECT      X.ParentCategoryId ,
                        X.DocumentLibraryCategoryId ,
                        CASE dbo.GetDocumentLibraryCategoryPermissionNames(X.DocumentLibraryCategoryId )
                               WHEN CAST('' AS NVARCHAR(1000))
                                    THEN CAST('Public' AS NVARCHAR(1000)) 
                                    ELSE dbo.GetDocumentLibraryCategoryPermissionNames(X.DocumentLibraryCategoryId ) 
                        END,
                        dbo.GetDocumentLibraryCategoryPermissionIds(X.DocumentLibraryCategoryId,CAST('' AS NVARCHAR(1000))) 
            FROM DocumentLibraryCategory X WHERE X.ParentCategoryId=0 AND X.SiteId = @SiteId
            UNION ALL
            SELECT  C.ParentCategoryId ,
                        C.DocumentLibraryCategoryId,
                        CASE dbo.GetDocumentLibraryCategoryPermissionNames(C.DocumentLibraryCategoryId )
                               WHEN CAST('' AS NVARCHAR(1000))
                                    THEN CAST('Inherit' AS NVARCHAR(1000)) 
                                    ELSE dbo.GetDocumentLibraryCategoryPermissionNames(C.DocumentLibraryCategoryId ) 
                        END,
                        dbo.GetDocumentLibraryCategoryPermissionIds(C.DocumentLibraryCategoryId,P.GroupIds)
            FROM DocumentLibraryCategory C INNER JOIN 
            PermissionsCTE P ON P.CategoryId=C.ParentCategoryId AND C.SiteId = @SiteId
      )
insert @t (categoryId, GroupNames, GroupIds)
SELECT CategoryId,GroupNames,GroupIds FROM PermissionsCTE
return
end

Open in new window

0
dij8Author Commented:
Unfortunately I have figured out now the GetDocumentLibraryCategoryPermissionsTree doesn't help me.  I need to check against a DocumentId.  I have a stored procedure that does in part what I want, I can sort out the actual permissions part within it later.  It returns a single int.  So in my main stored procedure I want to test against this new one checking if the returned value is greater than 0 or not.

I tried creating it as a Function but it won't allow temporary tables.  This stored procedure kind of needs them.

My main stored procedure does all sorts of joining of tables and uses various WHERE clauses to return a table of results.  One of these WHERE clauses needs to be whether my new stored procedure (or function if it can be done without temporary tables) returns a value greater than 0 or not:
WHERE ...
  OR (DD.[InheritPermissions] = 1 AND
    (EXEC GetDocumentLibraryDocumentPermissionsByParent @UId = @UserId, @DId = DD.DocumentLibraryDocumentDetailsId) > 0
  )

Of course this returns an error Incorrect syntax near the keyword 'EXEC'.

How can I get a result from the following stored procedure within my WHERE clause and test it against "> 0"?


CREATE PROCEDURE dbo.GetDocumentLibraryDocumentPermissionsByParent
(
	@UId int,
	@DId int,
	@Result int OUTPUT
)
AS
BEGIN
SELECT dlc.DocumentLibraryCategoryId, ParentCategoryId, 1 AS depth, dlc.DocumentLibraryCategoryId AS LeafId INTO #temp FROM documentlibrarydocumentcategory dldc
JOIN documentlibrarycategory dlc ON dldc.Documentlibrarycategoryid = dlc.Documentlibrarycategoryid
WHERE documentlibrarydocumentdetailsid = @DId
DECLARE @depth int
SELECT @depth = 1
WHILE EXISTS(SELECT TOP 1 LeafId FROM #temp WHERE depth = @depth)
BEGIN
	-- in here check perms on depth = @depth and exit if OK
	INSERT INTO #temp (DocumentLibraryCategoryId, ParentCategoryId, depth, LeafId)
	SELECT dlc.DocumentLibraryCategoryId, dlc.ParentCategoryId, @depth + 1 AS depth, #temp.LeafId 
	FROM documentlibrarycategory dlc 
	JOIN #temp ON dlc.Documentlibrarycategoryid = #temp.ParentCategoryId
	WHERE #temp.ParentCategoryId <> 0 AND #temp.depth = @depth
	SELECT @depth = @depth + 1
END
SELECT @Result = count(*) FROM #temp
DROP TABLE #temp
SELECT @Result
END
GO

Open in new window

0
dij8Author Commented:
I found a way of doing this that I don't think could have been answered by anyone here because of the limited knowledge of my specific setup.  Such is the nature of asking for help over the net.  The title to this question may well have led people down the wrong path as I thought at first this is how it must be done.

In looking into this I think under normal circumstances the best approach would be a TableValued Function so maradam gets the answer for future reference for anyone who finds this question when searching for a similar problem.

To demonstrate how I did it I have included the following code.  My select has changed a little and I managed to use "IN" queries within my WHERE clause.


	SELECT DD.DocumentLibraryDocumentDetailsId AS DocID
		, DLC.[CategoryName] AS CatName
		, DLC.[Position] AS CatPosition
		, DD.Author AS Author
		, DD.FileDate AS Created
		, DD.Title AS Title 
		, DD.Code AS Code
 
		INTO #tmpAllRecords 
		FROM DocumentLibraryDocumentDetails DD
			JOIN DocumentLibraryDocumentCategory DC ON DC.DocumentLibraryDocumentDetailsId = DD.DocumentLibraryDocumentDetailsId
			JOIN DocumentLibraryCategory DLC ON DLC.DocumentLibraryCategoryId = DC.DocumentLibraryCategoryId
		WHERE DD.[SiteId] = @SiteId
			AND ((DD.[InheritPermissions] = 0 AND
					(
						(SELECT COUNT(DU.UserId) FROM DocumentLibraryDocumentUser DU WHERE DU.DocumentLibraryDocumentDetailsId = DD.DocumentLibraryDocumentDetailsId) = 0
						AND (SELECT COUNT(DG.GroupId) FROM DocumentLibraryDocumentGroup DG WHERE DG.DocumentDetailsId = DD.DocumentLibraryDocumentDetailsId) = 0
					)
					OR @UserId IN (SELECT DU.UserId FROM DocumentLibraryDocumentUser DU WHERE DU.DocumentLibraryDocumentDetailsId = DD.DocumentLibraryDocumentDetailsId)
					OR @UserId IN (SELECT RGM.UserId FROM [RoleGroup] RG INNER JOIN [RoleGroupMember] RGM ON RGM.RoleGroupId = RG.RoleGroupId WHERE RG.RoleGroupId IN (SELECT DG.GroupId FROM DocumentLibraryDocumentGroup DG WHERE DG.DocumentDetailsId = DD.DocumentLibraryDocumentDetailsId))
				)
				OR (DD.[InheritPermissions] = 1 AND
					(
						(SELECT COUNT(DLCU.UserId) FROM DocumentLibraryCategoryUser DLCU WHERE DLCU.DocumentLibraryCategoryId = DLC.DocumentLibraryCategoryId) = 0
						AND (SELECT COUNT(DLCG.RoleGroupId) FROM DocumentLibraryCategoryRoleGroup DLCG WHERE DLCG.DocumentLibraryCategoryId = DLC.DocumentLibraryCategoryId) = 0
					)
					OR @UserId IN (SELECT DLCU.UserId FROM DocumentLibraryCategoryUser DLCU WHERE DLCU.DocumentLibraryCategoryId = DLC.DocumentLibraryCategoryId)
					OR @UserId IN (SELECT RGM.UserId FROM [RoleGroup] RG INNER JOIN [RoleGroupMember] RGM ON RGM.RoleGroupId = RG.RoleGroupId WHERE RG.RoleGroupId IN (SELECT DLCG.RoleGroupId FROM DocumentLibraryCategoryRoleGroup DLCG WHERE DLCG.DocumentLibraryCategoryId = DLC.DocumentLibraryCategoryId))
				)
			)

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.