2
\$\begingroup\$

The idea of the below code is that I feed back to a Gridview if the user has permission to view the property otherwise that property is not show.

The data is passed in via SessionParameters

USE [database]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[spSafeguardingActionPropertyByPermission]

@RegionID bigint
,@EmployeeID varchar(10)
,@PropertyID varchar(10)

AS

BEGIN

SELECT  TblA.PropertyID as A_PropertyID, 
        TblA.Propertyname as A_Propertyname , 
        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
        )

        END 
\$\endgroup\$
3
\$\begingroup\$

Does your EmployeeID and PropertyID parameters really need to be varchar(max), I find it hard to believe that an employee would have an ID with 8 000 characters. The same goes for the property ID. Change this to how many characters they are allowed to have (e.g., if an employee can only have an ID of 8 characters then use that, if it's 100 characters use that, and if it indeed is 8 000 characters, well then I suppose varchar(max) is fine)

I'd also advocate to use clearer aliases for your column. Instead of as PId you should alias it as as PropertyID.

This line is a bit unclear to me WHERE TblA.RegionID > 0. Can a Region have an ID of 0 or less-than 0?

I also think that the LIKE in this line TblA.PropertyID LIKE '%' + @PropertyID + '%' is used for the wrong purpose. It seems like what you really are doing is comparing the PropertyID of TblA.PropertyID to the parameter @PropertyID and in my opinion there should be no need of using wildcards here. Instead use the equals operator.

\$\endgroup\$
  • \$\begingroup\$ Changed varchar and name. In terms of like the user might only know part of the ID thus sadly wildcard searches. In a perfect world they would know it exactly. In terms of Region 0 is unassigned to somewhere at that point. Some boarder regions and it can be added to the system before it is assigned an area. Head Office monitor the 0 region properties to ensure they are assigned though! \$\endgroup\$ – indofraiser Jan 15 '14 at 15:55
  • \$\begingroup\$ Ok, but my other points still hold \$\endgroup\$ – Max Jan 15 '14 at 15:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.