CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008sql-server-2000
thevan
thevan

Reputation: 10354

Stored Procedure To Search the AccessRights given to the Users

I want to display the Access Rights given to the Users for the particular module.

I want to display the below records in the RoleAccess Table.

 ModuleName, SubModuleName, FunctionName,
CompanyID, RoleID, UnitID, FunctionID, ModuleID, SubModuleID,
Create_f, Update_f, Delete_f, Read_f and Approve_f.

If the RoleID does not exists in the RoleAccess Table, then it will display all the records as usual but with the flag columns as Zero.

I have witten one stored procedure but it displays the records based on the RoleID stored in the RoleAccess table. But I also want to display the Flags as 0 for the Roles not stored in the RoleAccess Table.

I want the Stored Procedure for this. Any one please help me.

My Stored Procedure is given below. In that Some of the Module has some two or more SubModules. I cannot display all the SubModules in the Search Results. If there is only one SubModule presents, then it will give the correct result. Please help me.

CREATE PROCEDURE [dbo].[SEC_GetAccessRight]
@CompanyID INT,
@UnitID Int,
@RoleID INT,
@ModuleID INT
AS
BEGIN

SELECT DISTINCT Module.ModuleName,
                SubModule.SubModuleName, 
                Functions.FunctionName,
                Functions.FunctionDescription,
                RoleAccess.CompanyID,
                RoleAccess.UnitID, 
                RoleAccess.RoleID, 
                Functions.FunctionID, 
                Module.ModuleID, 
                SubModule.SubModuleID,
                ISNULL(RoleAccess.Create_f, 0)      AS [CREATE],
                ISNULL(RoleAccess.Update_f, 0)      AS [UPDATE],
                ISNULL(RoleAccess.Delete_f, 0)      AS [DELETE],
                ISNULL(RoleAccess.Read_f, 0)        AS [READ],
                ISNULL(RoleAccess.Approval_f, 0)    AS [APPROVE]    
FROM Module INNER JOIN SubModule 
ON Module.Moduleid = SubModule.Moduleid INNER JOIN Functions
ON Module.Moduleid = Functions.Moduleid AND SubModule.SubModuleid = Functions.SubModuleid 
LEFT OUTER JOIN
roleaccess ON roleaccess.Moduleid = Module.Moduleid AND roleaccess.SubModuleid = SubModule.SubModuleid
AND Functions.Functionid = roleaccess.Functionid 
WHERE Module.ModuleID = (SELECT DISTINCT Module.ModuleID FROM Module INNER JOIN SubModule
on SubModule.ModuleID = Module.ModuleID LEFT OUTER JOIN
RoleAccess ON RoleAccess.ModuleID = Module.ModuleID AND RoleAccess.SubModuleID = SubModule.SubModuleID
WHERE Module.ModuleID = @ModuleID)
 AND RoleAccess.RoleID = @RoleID OR RoleAccess.RoleID IS NULL
AND RoleAccess.ModuleID IS NOT NULL AND RoleAccess.SubModuleID IS NULL
END

Upvotes: 0

Views: 165

Answers (1)

Sparky
Sparky

Reputation: 15075

Your code contains a bug...

You do a left join against RoleAccess, which is proper.

LEFT OUTER JOIN roleaccess ON roleaccess.Moduleid = Module.Moduleid 
         AND roleaccess.SubModuleid = SubModule.SubModuleid
AND Functions.Functionid = roleaccess.Functionid

By itself, this will cause rows to be returned from RoleAccess, and if no match is found, you will get an empty rowset from the table (which is what your ISNULL functions is looking for)

However, you later refer to ROLE ACCESS in the WHERE clause. This basically defeats the left join...

WHERE Module.ModuleID = @ModuleID)
 AND RoleAccess.RoleID = @RoleID OR RoleAccess.RoleID IS NULL
AND RoleAccess.ModuleID IS NOT NULL AND RoleAccess.SubModuleID IS NULL

Try moving the RoleID condition into the join, rather than the WHERE and see if that solves your problem...

LEFT JOIN should look like:

LEFT OUTER JOIN roleaccess ON roleaccess.Moduleid = Module.Moduleid 
             AND roleaccess.SubModuleid = SubModule.SubModuleid
             AND Functions.Functionid = roleaccess.Functionid 
             AND RoleAccess.RoleID = @RoleID

Upvotes: 1

Related Questions