在存储过程中循环并填充网格视图

Question

目标：选择"属性"并根据特定因素/权限显示在网格视图中

错误/问题：

我需要"循环"以仅在网格视图中显示用户的有效属性

SQL 中的代码

USE [database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
create PROCEDURE [dbo].[spSafeguardingActionPropertyByPermission]
@RegionID bigint
,@EmployeeID varchar(max)
,@PropertyID varchar(max)
AS
BEGIN
-- Easier one first
--Head Office, they see all
If @RegionID = 1  --then  -- Head office users
      BEGIN
      SELECT TblA.PropertyID as PId, TblA.Propertyname as PNa, TblB.FireSafety as FireSafety1, TblB.DisplayScreenEquipment as DSE  
      FROM TbPropertyDetails as TblA inner join TbPropertyDetailsSafeguarding as TblB on TblA.PropertyID = TblB.PropertyID 
      WHERE TblA.RegionID > 0 and TblA.PropertyID LIKE '%' + @PropertyID + '%'
      END
ELSE  
--Regional Manager who is not Region 1 (Head office) 
If @PropertyID = 99 --then
BEGIN 
      ---- First we want to see what properties the employee has permission to view
      -- Note @RegionID is used here to filter              
      SELECT TblA.PropertyID as PId, TblA.Propertyname as PNa, TblB.FireSafety as FireSafety1, TblB.DisplayScreenEquipment as DSE  
      FROM TbPropertyDetails as TblA inner join TbPropertyDetailsSafeguarding as TblB on TblA.PropertyID = TblB.PropertyID 
      WHERE TblA.RegionID = @RegionID and TblA.PropertyID LIKE '%' + @PropertyID + '%'    
END 
ELSE

--Not regional manager so only has access to specific sites
If @RegionID > 1 and @PropertyID <> 99 --then
--99 means they are not a regional manager
      BEGIN 
      ---- First we want to see what properties the employee has permission to view
      SELECT EmployeeID, PropertyID as tbPD_PropertyID 
      FROM tblPropertyViewPermissions
      WHERE EmployeeID = @EmployeeID 
      --loop here?? to get only those the user has access to
      SELECT TblA.PropertyID as PId, TblA.Propertyname as PNa, TblB.FireSafety as FireSafety1, TblB.DisplayScreenEquipment as DSE  
      FROM TbPropertyDetails as TblA inner join TbPropertyDetailsSafeguarding as TblB on TblA.PropertyID = TblB.PropertyID 
      -- END here??
      -- what to do here???!!! 
      -- Need to filter by PropertyID(s) that the user can see
      WHERE TblA.RegionID > 0 and TblA.PropertyID LIKE '%' + @PropertyID + '%'                  
      END
END

      -- If SQL can/ can not have If else (????) then capture all and show nothing with error message?

ASPX 中的代码 - 我见过我一直使用的 SessionParameter ，但我也看到了控制参数

<asp:SqlDataSource ID="SqlDataSource2" runat="server"
  ConnectionString="<%$ ConnectionStrings:GWmySQL %>"
  SelectCommand="spSafeguardingActionPropertyByPermission"
  SelectCommandType="StoredProcedure">
  <SelectParameters>    
    <asp:SessionParameter DefaultValue="" Name="EmployeeID"
      SessionField="EmployeeID" Type="string" />
    <asp:SessionParameter DefaultValue="" Name="PropertyID"
      SessionField="PropertyID" Type="string" />
    <asp:SessionParameter DefaultValue="" Name="RegionID"
      SessionField="RegionID" Type="string" />
  </SelectParameters>
</asp:SqlDataSource>

Answer 1

据我所知，您不需要循环，您可以使用单个查询来执行此操作：

SELECT  TblA.PropertyID as PId, 
        TblA.Propertyname as PNa, 
        TblB.FireSafety as FireSafety1, 
        TblB.DisplayScreenEquipment as DSE  
FROM    TbPropertyDetails as TblA 
        INNER JOIN TbPropertyDetailsSafeguarding as TblB 
            ON TblA.PropertyID = TblB.PropertyID 
WHERE   TblA.RegionID > 0 
AND     TblA.PropertyID LIKE '%' + @PropertyID + '%'  
AND     EXISTS
        (   SELECT  1
            FROM    tblPropertyViewPermissions AS pvp
            WHERE   pvp.EmployeeID = @EmployeeID    
            AND     pvp.PropertyID = TblA.PropertyID
        );

最后一个 EXISTS 子句只是检查用户是否有权查看作为参数传递的属性。