title: Stored Procedure To Search the AccessRights given to the Users
body: <p>I want to display the Access Rights given to the Users for the particular module.</p>
  
  <ul>
  <li>I have Seven Tables such as RoleAccess, Roles, Functions, Module, SubModule, Company and Unit. RoleAccess is the Main Table. </li>
  <li>The AccessRights given will be stored in the RoleAccess Table only.  </li>
  <li>RoleAccess Table has the following columns such as RoleID, CompanyID, UnitID, FunctionID, ModuleID, SubModuleID, Create, Update, Delete, Read, Approve. Here Create_f, Update_f, Delete_f, Read_f and Approve_f are flags.  </li>
  <li>Company Table has two columns such as CompanyID and CompanyName.  </li>
  <li>Unit Table has three columns such as UnitID, UnitName and CompanyID.  </li>
  <li>Roles Table has four columns such as RoleID, RoleName, CompanyID and UnitID.  </li>
  <li>Module Table has two columns such as ModuleID and ModuleName.  </li>
  <li>SubModule Table has three columns such as ModuleID, SubModuleID, SubModuleName.  </li>
  <li>Functions Table has five columns such as FunctionID, FunctionName, ModuleID and SubModuleID.</li>
  </ul>
  
  <p>I want to display the below records in the RoleAccess Table.</p>
  
  <pre><code> ModuleName, SubModuleName, FunctionName,
  CompanyID, RoleID, UnitID, FunctionID, ModuleID, SubModuleID,
  Create_f, Update_f, Delete_f, Read_f and Approve_f.  
  </code></pre>
  
  <p>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.</p>
  
  <p>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.  </p>
  
  <p>I want the Stored Procedure for this. Any one please help me.  </p>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>