SQL Look up in a Stored Procedure across three tables

Question

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 

Answer 1

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.