Code:
ALTER PROCEDURE [sde].[PROPERTY_LIST]
@ACTIVE_USER_ID INTEGER = 0,
@PARCEL_NO VARCHAR(50) = '',
@BOUNDARY_AREA VARCHAR(40) = '',
@NUM_KEY VARCHAR(10) = '',
@LIS_KEY VARCHAR(50) = '',
@OWNER_NAME VARCHAR(500) = '',
@PARCEL_PORTION_NO VARCHAR(10) = '',
@CELL_NO VARCHAR(50) = '',
@ATTRIB_CODE VARCHAR(12) = '',
@EXTENSION INTEGER = 0,
@FUNC_KEY VARCHAR(30) = '',
@PROVINCE_ID INTEGER = 0,
@STREET_NO VARCHAR(500) = '',
@STREET_NAME VARCHAR(500) = '',
@ID INTEGER = 0
AS
SET @PARCEL_NO = ISNULL(@PARCEL_NO,'')
SET @BOUNDARY_AREA = ISNULL(@BOUNDARY_AREA,'')
SET @LIS_KEY = ISNULL(@LIS_KEY,'')
SET @OWNER_NAME = ISNULL(@OWNER_NAME,'')
SET @PARCEL_PORTION_NO = ISNULL(@PARCEL_PORTION_NO,'')
SET @CELL_NO = ISNULL(@CELL_NO,'')
SET @ATTRIB_CODE = ISNULL(@ATTRIB_CODE,'')
SET @FUNC_KEY = ISNULL(@FUNC_KEY,'')
SET @STREET_NO = ISNULL(@STREET_NO,'')
SET @STREET_NAME = ISNULL(@STREET_NAME,'')
SET @EXTENSION = ISNULL(@EXTENSION, 0)
IF @ACTIVE_USER_ID <> 0
BEGIN
IF @ID = 0
BEGIN
DECLARE @strSQL NVARCHAR(4000)
SET @strSQL =
'SELECT DISTINCT TOP 200 PARCEL_PORTION_NO,
PROPERTY_ID, PARCEL_NO AS PARCEL_NO, ROLL_REMARK, RATEABLE, EXTENSION, FUNC_KEY, ISNULL(P.NOTIFICATION_EXIST, ''FALSE'') AS NOTIFICATION_EXIST, ISNULL(P.SUPPL_EXIST, ''FALSE'') AS SUPPL_EXIST, NON_DISCREET_ADD, NON_DISCREET_VALID, P.PROBLEM,
COMPL_BOUNDARY_AREA,
CASE (CASE CHARINDEX(''/'', PARCEL_PORTION_NO)
WHEN 0 THEN LEN(LTRIM(RTRIM(ISNULL(PARCEL_PORTION_NO,''''))))
ELSE LEN(LTRIM(RTRIM(ISNULL( SUBSTRING(PARCEL_PORTION_NO,1,CHARINDEX(''/'', PARCEL_PORTION_NO)-1), ''''))))
END)
WHEN 1 THEN CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_NO,'''')))) + '' '' +
CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_PORTION_NO, ''''))))
WHEN 2 THEN CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_NO,'''')))) + '' '' +
CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_PORTION_NO, ''''))))
ELSE CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_NO,'''')))) +
CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_PORTION_NO, ''''))))
END AS COMPL_PARCEL_PORTION, PROP_DESCRIPTION AS [DESCRIPTION],
CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_NO,'''')))) +
CONVERT(VARCHAR, LTRIM(RTRIM(ISNULL(PARCEL_PORTION_NO, '''')))) AS COMPL_PARCEL_PORTION_OLD,
NUM_KEY, BOUNDARY_AREA, P.ATTRIB_CODE, LIS_KEY, PROVINCE_ID, AUTHORITY_ID, CELL_NO, OWN_NAME,
TITLE_DEED_NO, PROPERTY_KEY, CURRENT_VALUES, SITE_VALUE, IMPROVED_VALUE, ACTUAL_EXTENT, PROVINCE, AUTHORITY, PROP_ADDRESS, STREET_NO, STREET_NAME,
EAST_OF, WEST_OF, NORTH_OF, SOUTH_OF, DENSITY, PROP_SOURCE_ID, PROP_CATEGORY_ID, LEGAL_AREA, ACTIVE, PARCEL_CLASS,
PARCEL_PORTION_NO, PARCEL_STATUS, PARCEL_REMARK, PARCEL_CAPTURED, PARCEL_MODIFIED, PARCEL_NAME, BUILDING_FARM_NAME,
SG_DIAGRAM_NO, GP_SUBDIVISION_NO, GENERAL_PLAN_NO, MUNIC_STATUS, PARCEL_TYPE, HISTORY, SUBDIVISION_COMPONENT, CONSOLIDATION_COMPONENT, REG_DATE,
(
SELECT TOP 1 C.PROP_CATEGORY_DESCR
FROM SDE.PROP_ATTRIB_PATTERN_CATEGORY A
INNER JOIN SDE.LU_PROP_CATEGORY_NEW C
ON C.PROP_CATEGORY_ID = A.CATEGORY_ID
WHERE SUBSTRING(A.ATTRIB_CODE_PATTERN, 1, 8) = SUBSTRING(P.ATTRIB_CODE, 1, 8)
) AS PROP_CATEGORY_DESCR
FROM sde.PROPERTY_SUMMARY P
WHERE 1=1 --P.ARCHIVE_DATE IS NULL
'
-- PROP_CATEGORY_DESCR added above by Jacob Jackson 200708 to include category as defined by new legislation
IF @EXTENSION <> 0
BEGIN SET @strSQL = @strSQL + ' AND P.EXTENSION = ' + CONVERT(VARCHAR, @EXTENSION)
END
IF @BOUNDARY_AREA <> ''
BEGIN SET @strSQL = @strSQL + ' AND P.BOUNDARY_AREA LIKE ''' + @BOUNDARY_AREA + ''''
END
IF @OWNER_NAME <> ''
BEGIN SET @strSQL = @strSQL + ' AND P.OWN_NAME LIKE ''' + @OWNER_NAME + ''''
END
IF @PARCEL_NO <> ''
BEGIN SET @strSQL = @strSQL + ' AND P.PARCEL_NO LIKE ''%' + @PARCEL_NO + '%'''
END
IF @LIS_KEY <> ''
BEGIN SET @strSQL = @strSQL + ' AND P.LIS_KEY LIKE ''' + @LIS_KEY + '%'''
END
IF @PARCEL_PORTION_NO <> ''
BEGIN SET @strSQL = @strSQL + ' AND P.PARCEL_PORTION_NO LIKE ''' + @PARCEL_PORTION_NO + ''''
END
IF @CELL_NO <> ''
BEGIN SET @strSQL = @strSQL + ' AND P.CELL_NO = ' + @CELL_NO
END
IF @ATTRIB_CODE = '' OR @ATTRIB_CODE = '__________'
BEGIN
SET @strSQL = @strSQL + ' AND (P.ATTRIB_CODE IS NULL OR P.ATTRIB_CODE LIKE ''%'') '
END
ELSE
BEGIN
SET @strSQL = @strSQL + ' AND ISNULL(P.ATTRIB_CODE, '''') LIKE '''+ @ATTRIB_CODE + '%'' '
END
IF @FUNC_KEY <> ''
BEGIN
SET @strSQL = @strSQL + ' AND P.FUNC_KEY LIKE ''' + CONVERT(VARCHAR, @FUNC_KEY) + ''''
END
/* IF @STREET_NO <> ''
BEGIN SET @strSQL = @strSQL + ' AND P.PROP_ADDRESS LIKE ''' + CONVERT(VARCHAR, @STREET_NO) + '%'''
END
IF @STREET_NAME <> ''
BEGIN SET @strSQL = @strSQL + ' AND P.PROP_ADDRESS LIKE ''' + CONVERT(VARCHAR, @STREET_NAME) + '%'''
END
*/
IF @STREET_NO <> ''
BEGIN
SET @strSQL = @strSQL + ' AND (P.PROP_ADDRESS LIKE ''' + CONVERT(VARCHAR, @STREET_NO) + '%''
OR P.PROP_ADDRESS LIKE ''%' + CONVERT(VARCHAR, @STREET_NO) + '%'' ) '
END
IF @STREET_NAME <> ''
BEGIN
SET @strSQL = @strSQL + ' AND P.PROP_ADDRESS LIKE ''%' + CONVERT(VARCHAR, @STREET_NAME) + '%'''
END
IF @PROVINCE_ID <> 0
BEGIN
SET @strSQL = @strSQL + ' AND P.PROVINCE_ID = ' + CONVERT(VARCHAR, @PROVINCE_ID)
END
/* CURRENT QUICK FIX FOR GUEST & FINANCE USERS TO PREVENT THEM FROM SEEING PROPERTIES WITHOUT CURRENT_VALUES. TO BE REMOVED AT A LATER DATE*/
DECLARE @IS_GUEST_FINANCE INTEGER
SET @IS_GUEST_FINANCE = (
SELECT CASE WHEN LOGIN_NAME IN ('GUEST', 'FINANCE')
THEN 1
ELSE 0
END
FROM SDE.USER_INFO
WHERE [USER_ID] = @ACTIVE_USER_ID
)
IF ISNULL(@IS_GUEST_FINANCE, 0) <> 0
BEGIN
SET @strSQL = @strSQL + ' AND P.CURRENT_VALUES IS NOT NULL '
END
SET @strSQL = @strSQL + ' AND P.ARCHIVE_DATE IS NULL ' + ' ORDER BY P.COMPL_BOUNDARY_AREA, P.COMPL_PARCEL_PORTION, P.PROPERTY_KEY'
EXECUTE SP_EXECUTESQL @strSQL
END
ELSE -- SPECIFIC RECORD
BEGIN
SELECT PP.*,
ISNULL(P.NOTIFICATION_EXIST, 'FALSE') AS NOTIFICATION_EXIST, ISNULL(P.SUPPL_EXIST, 'FALSE') AS SUPPL_EXIST, NON_DISCREET_ADD, NON_DISCREET_VALID, P.PROBLEM,
PARCEL_NO AS PARCEL_NO, ROLL_REMARK, RATEABLE, FUNC_KEY, PROP_DESCRIPTION AS [DESCRIPTION],
EXTENSION, NUM_KEY, BOUNDARY_AREA, P.ATTRIB_CODE, LIS_KEY, PROVINCE_ID, AUTHORITY_ID, CELL_NO, OWN_NAME,
TITLE_DEED_NO, PROPERTY_KEY, CURRENT_VALUES, SITE_VALUE, IMPROVED_VALUE, IMPROVEMENTS_VALUE, ACTUAL_EXTENT, PROVINCE, AUTHORITY, PROP_ADDRESS, STREET_NO, STREET_NAME,
EAST_OF, WEST_OF, NORTH_OF, SOUTH_OF, DENSITY, PROP_SOURCE_ID, PROP_CATEGORY_ID, LEGAL_AREA, ACTIVE, PARCEL_CLASS,
PARCEL_PORTION_NO, PARCEL_STATUS, PARCEL_REMARK, PARCEL_CAPTURED, PARCEL_MODIFIED, PARCEL_NAME, BUILDING_FARM_NAME,
SG_DIAGRAM_NO, GP_SUBDIVISION_NO, GENERAL_PLAN_NO, MUNIC_STATUS, PARCEL_TYPE, HISTORY, SUBDIVISION_COMPONENT, CONSOLIDATION_COMPONENT, REG_DATE,
PURCHASE_DATE, PURCHASE_PRICE, LAST_SUPPL_NO, OLD_SUPPL_NO, VALUES_EFFECTIVE, ACTIVE_VAL_PROCESSED_DATE,
(
SELECT TOP 1 C.PROP_CATEGORY_DESCR
FROM SDE.PROP_ATTRIB_PATTERN_CATEGORY A
INNER JOIN SDE.LU_PROP_CATEGORY_NEW C
ON C.PROP_CATEGORY_ID = A.CATEGORY_ID
WHERE SUBSTRING(A.ATTRIB_CODE_PATTERN, 1, 8) = SUBSTRING(P.ATTRIB_CODE, 1, 8)
) AS PROP_CATEGORY_DESCR
FROM sde.PROPERTY_SUMMARY P
INNER JOIN SDE.VW_PROPERTY_PROCESSING_PENDING PP ON P.PROPERTY_ID = PP.PROPERTY_ID
WHERE P.PROPERTY_ID = @ID
END
END
ELSE
BEGIN
PRINT 'PLEASE SPECIFY AN ACTIVE_USER_ID'
END
Help Optimizing the Following SP
Reply With Quote