oMiKeY oMiKeY - 3 months ago 8
SQL Question

SQL Server : query simplification - many joins and a giant WHERE section

I am trying to run a stored procedure, but it keeps timing out from using too much ram. The server its being run on only has 1.5GB of ram to work with. Here is the original query:

CREATE PROCEDURE [dbo].[rpt_Registrations_Std]
@Action nvarchar(50) = '', -- Action is field name for now
@DATE_OPRT_FROM datetime = null,
@DATE_OPRT_TO datetime = null,
@DATE_EFFECTIVE_FROM datetime = null,
@DATE_EFFECTIVE_TO datetime = null,
@DATE_MODIFIED_FROM datetime = null,
@DATE_MODIFIED_TO datetime = null,
@ASSET_NO nvarchar(4000) = '',
@ASSET_NO_FROM nvarchar(20) = '',
@ASSET_NO_TO nvarchar(20) = '',
@HOLDER_CD nvarchar(4000) = '',
@HOLDER_CD_FROM nvarchar(10) = '',
@HOLDER_CD_TO nvarchar(10) = '',
@SITE_CD nvarchar(4000) = '',
@SITE_CD_FROM nvarchar(10) = '',
@SITE_CD_TO nvarchar(10) = '',
@ASSETCODE nvarchar(4000) = '',
@ASSETCODE_FROM nvarchar(10) = null,
@ASSETCODE_TO nvarchar(10) = null,
@FUND_CD nvarchar(4000) = '',
@FUND_CD_FROM nvarchar(10) = '',
@FUND_CD_TO nvarchar(10) = '',
@PO_NO nvarchar(4000) = '',
@PO_NO_FROM nvarchar(15) = '',
@PO_NO_TO nvarchar(15) = '',
@STATUS nvarchar(4000) = '',
@STATUS_FROM int = null,
@STATUS_TO int = null,
@UNITPRICE_FROM money = null,
@UNITPRICE_TO money = null,
@CAPITALIZED bit = Null
AS
BEGIN
select cast(data as nvarchar) ASSET_NO INTO #asset_nos from dbo.split(@ASSET_NO,',')
where data is not null
select cast(data as nvarchar) HOLDER_CD INTO #holders from dbo.split(@HOLDER_CD,',')
where data is not null
select cast(data as nvarchar) SITE_CD INTO #site from dbo.split(@SITE_CD,',')
where data is not null
select cast(data as nvarchar) ASSETCODE INTO #assetcode from dbo.split(@ASSETCODE,',')
where data is not null
select cast(data as nvarchar) FUND_CD INTO #fundcode from dbo.split(@FUND_CD,',')
where data is not null
select cast(data as nvarchar) PO_NO INTO #pono from dbo.split(@PO_NO,',')
where data is not null
select cast(data as int) [STATUS] INTO #status from dbo.split(@STATUS,',')
where data is not null


DECLARE @FIELD nvarchar(20)
SET @FIELD = @Action

--- FIELD Def for report From here
SELECT
a.ASSET_NO
,a.DATE_RECEIVED as DATE_OPRT
,a.DATE_EFFECTIVE_REGISTERED as DATE_EFFECTIVE
,a.DATE_REGISTERED as DATE_MODIFIED
,a.USER_ID_REGIST as USER_ID_MODIFY
,a.DESC_1
,a.DESC_2
,a.DESC_AX
,a.DESC_1
+ CASE WHEN IsNull(a.DESC_2, '') = '' THEN '' ELSE ' ' + a.DESC_2 END
+ CASE WHEN IsNull(a.DESC_AX, '') = '' THEN '' ELSE ' ' + a.DESC_AX END
as ASSET_DESC
,a.MANUFACTURER
,a.MODEL
,a.SERIAL_NO
,a.WEIGHT
,a.ASSETCODE
,a.SITE_CD
,a.ROOM_CD
,a.BUILDING_CD
,a.HOLDER_CD
,a.HOLDER_DESC
,a.STATUS
,Left(a.STATUS_DESC, 10) as STATUS_DESC
,ac.CODE_DESCRIPTION
,a.RM_DESC_1 AS ROOM_DESC
,a.SITE_NAME_1 AS SITE_NAME
,a.BUILDING_NAME
,a.DATE_RECEIVED
,fin.DATE_INSERVICE
,fin.ORIG_COST
,fin.QTY
,fin.PM_FUND_CD as FUND_CD
,fin.CAPITALIZED
,fnd.FUND_DESC
,a.PM_PO_NO as PO_NO

-- Initial values
,u.ORIG_COST AS INIT_COST
,ui.ASSETCODE AS INIT_ASSETCODE
,uR.SITE_CD AS INIT_SITE_CD
,u.ROOM_CD AS INIT_ROOM_CD
,uR.BUILDING_CD AS INIT_BUILDING_CD
,uR.RM_DESC_1 AS INIT_ROOM_DESC
,uR.BUILDING_NAME AS INIT_BUILDING_NAME
,uR.SITE_NAME_1 AS INIT_SITE_NAME
,u.HOLDER_CD AS INIT_HOLDER_CD
,uH.ORG_DESC AS INIT_HOLDER_DESC
,uac.CODE_DESCRIPTION AS INIT_CODE_DESCRIPTION

FROM vAsset as a
LEFT JOIN vAsset_Rgst_Info as u ON a.ASSET_ID = u.ASSET_ID
LEFT JOIN tAssetCode as ac ON a.ASSETCODE = ac.ASSETCODE
LEFT JOIN tAssetFin as fin ON a.ASSET_NO = fin.ASSET_NO
LEFT JOIN tFunding as fnd ON fin.PM_FUND_CD = fnd.FUND_CD
LEFT JOIN tItemCatalog as ui ON u.ITEM_CD = ui.ITEM_CD
LEFT jOIN tAssetCode as uac ON ui.ASSETCODE = uac.ASSETCODE
LEFT JOIN vRoom as uR ON u.ROOM_CD = uR.ROOM_CD
LEFT JOIN tOrganization as uH ON u.HOLDER_CD = uH.ORG_CD

WHERE
( @DATE_OPRT_FROM is null OR a.DATE_RECEIVED >= @DATE_OPRT_FROM )
AND ( @DATE_OPRT_TO is null OR a.DATE_RECEIVED <= @DATE_OPRT_TO )
AND ( @DATE_EFFECTIVE_FROM is null OR a.DATE_EFFECTIVE_REGISTERED >= @DATE_EFFECTIVE_FROM )
AND ( @DATE_EFFECTIVE_TO is null OR a.DATE_EFFECTIVE_REGISTERED <= @DATE_EFFECTIVE_TO )
AND ( @DATE_MODIFIED_FROM is null OR a.DATE_REGISTERED >= @DATE_MODIFIED_FROM )
AND ( @DATE_MODIFIED_TO is null OR a.DATE_REGISTERED <= @DATE_MODIFIED_TO )
AND ( @ASSET_NO = '' OR u.ASSET_NO in ( SELECT ASSET_NO from #asset_nos ))
AND ( @ASSET_NO_FROM = '' OR u.ASSET_NO >= @ASSET_NO_FROM )
AND ( @ASSET_NO_TO = '' OR u.ASSET_NO <= @ASSET_NO_TO )
AND ( @HOLDER_CD = '' OR a.HOLDER_CD in ( SELECT HOLDER_CD from #holders ))
AND ( @HOLDER_CD_FROM = '' OR a.HOLDER_CD >= @HOLDER_CD_FROM )
AND ( @HOLDER_CD_TO = '' OR a.HOLDER_CD <= @HOLDER_CD_TO )
AND ( @SITE_CD = '' OR a.SITE_CD in ( SELECT SITE_CD from #site ))
AND ( @SITE_CD_FROM = '' OR a.SITE_CD >= @SITE_CD_FROM )
AND ( @SITE_CD_TO = '' OR a.SITE_CD <= @SITE_CD_TO )
AND ( @ASSETCODE = '' OR a.ASSETCODE in ( SELECT ASSETCODE from #assetcode ))
AND ( @ASSETCODE_FROM is null OR a.ASSETCODE >= @ASSETCODE_FROM )
AND ( @ASSETCODE_TO is null OR a.ASSETCODE <= @ASSETCODE_TO )
AND ( @FUND_CD = '' OR PM_FUND_CD in ( SELECT FUND_CD from #fundcode ))
AND ( @FUND_CD_FROM = '' OR fin.PM_FUND_CD >= @FUND_CD_FROM )
AND ( @FUND_CD_TO = '' OR fin.PM_FUND_CD <= @FUND_CD_TO )
AND ( @PO_NO = '' OR a.PM_PO_NO in ( SELECT PO_NO from #pono ))
AND ( @PO_NO_FROM = '' OR a.PM_PO_NO >= @PO_NO_FROM )
AND ( @PO_NO_TO = '' OR a.PM_PO_NO <= @PO_NO_TO )
AND ( @STATUS = '' OR a.STATUS in ( SELECT [STATUS] from #status ))
AND ( @STATUS_FROM is null OR a.STATUS >= @STATUS_FROM )
AND ( @STATUS_TO is null OR a.STATUS <= @STATUS_TO )
AND ( @UNITPRICE_FROM is null OR fin.UNIT_COST >= @UNITPRICE_FROM )
AND ( @UNITPRICE_TO is null OR fin.UNIT_COST <= @UNITPRICE_TO )
AND ( @CAPITALIZED is null OR fin.CAPITALIZED = @CAPITALIZED )
END -- proc


I added some subquerying to speed it up, but it still times out:

ALTER PROCEDURE [dbo].[rpt_Registrations_Std]
@Action nvarchar(50) = '', -- Action is field name for now
@DATE_OPRT_FROM datetime = null,
@DATE_OPRT_TO datetime = null,
@DATE_EFFECTIVE_FROM datetime = null,
@DATE_EFFECTIVE_TO datetime = null,
@DATE_MODIFIED_FROM datetime = null,
@DATE_MODIFIED_TO datetime = null,
@ASSET_NO nvarchar(4000) = '',
@ASSET_NO_FROM nvarchar(20) = '',
@ASSET_NO_TO nvarchar(20) = '',
@HOLDER_CD nvarchar(4000) = '',
@HOLDER_CD_FROM nvarchar(10) = '',
@HOLDER_CD_TO nvarchar(10) = '',
@SITE_CD nvarchar(4000) = '',
@SITE_CD_FROM nvarchar(10) = '',
@SITE_CD_TO nvarchar(10) = '',
@ASSETCODE nvarchar(4000) = '',
@ASSETCODE_FROM nvarchar(10) = null,
@ASSETCODE_TO nvarchar(10) = null,
@FUND_CD nvarchar(4000) = '',
@FUND_CD_FROM nvarchar(10) = '',
@FUND_CD_TO nvarchar(10) = '',
@PO_NO nvarchar(4000) = '',
@PO_NO_FROM nvarchar(15) = '',
@PO_NO_TO nvarchar(15) = '',
@STATUS nvarchar(4000) = '',
@STATUS_FROM int = null,
@STATUS_TO int = null,
@UNITPRICE_FROM money = null,
@UNITPRICE_TO money = null,
@CAPITALIZED bit = Null
AS
BEGIN

select cast(data as nvarchar) ASSET_NO INTO #asset_nos from dbo.split(@ASSET_NO,',')
where data is not null
select cast(data as nvarchar) HOLDER_CD INTO #holders from dbo.split(@HOLDER_CD,',')
where data is not null
select cast(data as nvarchar) SITE_CD INTO #site from dbo.split(@SITE_CD,',')
where data is not null
select cast(data as nvarchar) ASSETCODE INTO #assetcode from dbo.split(@ASSETCODE,',')
where data is not null
select cast(data as nvarchar) FUND_CD INTO #fundcode from dbo.split(@FUND_CD,',')
where data is not null
select cast(data as nvarchar) PO_NO INTO #pono from dbo.split(@PO_NO,',')
where data is not null
select cast(data as int) [STATUS] INTO #status from dbo.split(@STATUS,',')
where data is not null



DECLARE @FIELD nvarchar(20)
SET @FIELD = @Action

--- FIELD Def for report From here
SELECT
a.ASSET_NO

,a.DATE_RECEIVED as DATE_OPRT
,a.DATE_EFFECTIVE_REGISTERED as DATE_EFFECTIVE
,a.DATE_REGISTERED as DATE_MODIFIED
,a.USER_ID_REGIST as USER_ID_MODIFY

,a.DESC_1
,a.DESC_2
,a.DESC_AX
,a.DESC_1
+ CASE WHEN IsNull(a.DESC_2, '') = '' THEN '' ELSE ' ' + a.DESC_2 END
+ CASE WHEN IsNull(a.DESC_AX, '') = '' THEN '' ELSE ' ' + a.DESC_AX END
as ASSET_DESC
,a.MANUFACTURER
,a.MODEL
,a.SERIAL_NO
,a.WEIGHT
,a.ASSETCODE
,a.SITE_CD
,a.ROOM_CD
,a.BUILDING_CD
,a.HOLDER_CD
,a.HOLDER_DESC
,a.STATUS
,Left(a.STATUS_DESC, 10) as STATUS_DESC
,ac.CODE_DESCRIPTION
,a.RM_DESC_1 AS ROOM_DESC
,a.SITE_NAME_1 AS SITE_NAME
,a.BUILDING_NAME
,a.DATE_RECEIVED
,fin.DATE_INSERVICE
,fin.ORIG_COST
,fin.QTY
,fin.PM_FUND_CD as FUND_CD
,fin.CAPITALIZED
,fnd.FUND_DESC
,a.PM_PO_NO as PO_NO

-- Initial values
,u.ORIG_COST AS INIT_COST
,ui.ASSETCODE AS INIT_ASSETCODE
,uR.SITE_CD AS INIT_SITE_CD
,u.ROOM_CD AS INIT_ROOM_CD
,uR.BUILDING_CD AS INIT_BUILDING_CD
,uR.RM_DESC_1 AS INIT_ROOM_DESC
,uR.BUILDING_NAME AS INIT_BUILDING_NAME
,uR.SITE_NAME_1 AS INIT_SITE_NAME
,u.HOLDER_CD AS INIT_HOLDER_CD
,uH.ORG_DESC AS INIT_HOLDER_DESC
,uac.CODE_DESCRIPTION AS INIT_CODE_DESCRIPTION

FROM (SELECT
a.ASSET_NO
,a.ASSET_ID
,a.DATE_EFFECTIVE_REGISTERED
,a.DATE_REGISTERED
,a.USER_ID_REGIST
,a.DESC_1
,a.DESC_2
,a.DESC_AX
,a.MANUFACTURER
,a.MODEL
,a.SERIAL_NO
,a.WEIGHT
,a.ASSETCODE
,a.SITE_CD
,a.ROOM_CD
,a.BUILDING_CD
,a.HOLDER_CD
,a.HOLDER_DESC
,a.STATUS
,a.STATUS_DESC
,a.RM_DESC_1
,a.SITE_NAME_1
,a.BUILDING_NAME
,a.DATE_RECEIVED
,a.PM_PO_NO

FROM vAsset as a WHERE ASSET_NO IN (
SELECT ASSET_NO FROM vAsset as a
WHERE
( @DATE_OPRT_FROM is null OR a.DATE_RECEIVED >= @DATE_OPRT_FROM )
AND ( @DATE_OPRT_TO is null OR a.DATE_RECEIVED <= @DATE_OPRT_TO )
AND ( @DATE_EFFECTIVE_FROM is null OR a.DATE_EFFECTIVE_REGISTERED >= @DATE_EFFECTIVE_FROM )
AND ( @DATE_EFFECTIVE_TO is null OR a.DATE_EFFECTIVE_REGISTERED <= @DATE_EFFECTIVE_TO )
AND ( @DATE_MODIFIED_FROM is null OR a.DATE_REGISTERED >= @DATE_MODIFIED_FROM )
AND ( @DATE_MODIFIED_TO is null OR a.DATE_REGISTERED <= @DATE_MODIFIED_TO )
AND ( @HOLDER_CD = '' OR a.HOLDER_CD in ( SELECT HOLDER_CD from #holders ))
AND ( @HOLDER_CD_FROM = '' OR a.HOLDER_CD >= @HOLDER_CD_FROM )
AND ( @HOLDER_CD_TO = '' OR a.HOLDER_CD <= @HOLDER_CD_TO )
AND ( @SITE_CD = '' OR a.SITE_CD in ( SELECT SITE_CD from #site ))
AND ( @SITE_CD_FROM = '' OR a.SITE_CD >= @SITE_CD_FROM )
AND ( @SITE_CD_TO = '' OR a.SITE_CD <= @SITE_CD_TO )
AND ( @ASSETCODE = '' OR a.ASSETCODE in ( SELECT ASSETCODE from #assetcode ))
AND ( @ASSETCODE_FROM is null OR a.ASSETCODE >= @ASSETCODE_FROM )
AND ( @ASSETCODE_TO is null OR a.ASSETCODE <= @ASSETCODE_TO )
AND ( @PO_NO = '' OR a.PM_PO_NO in ( SELECT PO_NO from #pono ))
AND ( @PO_NO_FROM = '' OR a.PM_PO_NO >= @PO_NO_FROM )
AND ( @PO_NO_TO = '' OR a.PM_PO_NO <= @PO_NO_TO )
AND ( @STATUS = '' OR a.STATUS in ( SELECT [STATUS] from #status ))
AND ( @STATUS_FROM is null OR a.STATUS >= @STATUS_FROM )
AND ( @STATUS_TO is null OR a.STATUS <= @STATUS_TO )
)) as a
LEFT JOIN vAsset_Rgst_Info as u ON a.ASSET_ID = u.ASSET_ID
LEFT JOIN tAssetCode as ac ON a.ASSETCODE = ac.ASSETCODE
LEFT JOIN tAssetFin as fin ON a.ASSET_NO = fin.ASSET_NO
LEFT JOIN tFunding as fnd ON fin.PM_FUND_CD = fnd.FUND_CD
LEFT JOIN tItemCatalog as ui ON u.ITEM_CD = ui.ITEM_CD
LEFT jOIN tAssetCode as uac ON ui.ASSETCODE = uac.ASSETCODE
LEFT JOIN vRoom as uR ON u.ROOM_CD = uR.ROOM_CD
LEFT JOIN tOrganization as uH ON u.HOLDER_CD = uH.ORG_CD

WHERE
( @ASSET_NO = '' OR u.ASSET_NO in ( SELECT ASSET_NO from #asset_nos ))
AND ( @ASSET_NO_FROM = '' OR u.ASSET_NO >= @ASSET_NO_FROM )
AND ( @ASSET_NO_TO = '' OR u.ASSET_NO <= @ASSET_NO_TO )
AND ( @FUND_CD = '' OR PM_FUND_CD in ( SELECT FUND_CD from #fundcode ))
AND ( @FUND_CD_FROM = '' OR fin.PM_FUND_CD >= @FUND_CD_FROM )
AND ( @FUND_CD_TO = '' OR fin.PM_FUND_CD <= @FUND_CD_TO )
AND ( @UNITPRICE_FROM is null OR fin.UNIT_COST >= @UNITPRICE_FROM )
AND ( @UNITPRICE_TO is null OR fin.UNIT_COST <= @UNITPRICE_TO )
AND ( @CAPITALIZED is null OR fin.CAPITALIZED = @CAPITALIZED )



END -- proc


I'm at a loss now. Seems there is simply too much data to process on too small a server.

Answer

The modifications are:

1) create a new scalar function to test if data exist in delimited comma text and no need to create #temp tables to check if data exist.

    -- test if token exis in delimited comma string, use spli function
    create FUNCTION [dbo].[inList]
    (

          @delimited NVARCHAR(MAX) ,
          @token NVARCHAR(100) 
      ) 
        RETURNS NVARCHAR(100) 
        as
        begin
        declare @retval NVARCHAR(100) 
        select @retval=data from 
         dbo.split(@delimited) t
        where t.data=@token
        return @retval
        end
        go

we replace the expression in where condition, as in the following example:

    --   AND ( @ASSET_NO = '' OR u.ASSET_NO in ( SELECT ASSET_NO from #asset_nos ))

modified to:

            AND ( @ASSET_NO = '' OR u.ASSET_NO = dbo.inlist(@ASSET_NO,u.ASSET_NO))

and the same for all delimited lists

2) As you use many null parameters, recompile SP before execution to get fresh execution plan ( WITH RECOMPILE)

3) The modified code:

    Alter PROCEDURE [dbo].[rpt_Registrations_Std]
        @Action nvarchar(50) = '',  -- Action is field name for now
        @DATE_OPRT_FROM datetime = null,
        @DATE_OPRT_TO datetime = null,
        @DATE_EFFECTIVE_FROM datetime = null,
        @DATE_EFFECTIVE_TO datetime = null,
        @DATE_MODIFIED_FROM datetime = null,
        @DATE_MODIFIED_TO datetime = null,
        @ASSET_NO nvarchar(4000) = '',
        @ASSET_NO_FROM nvarchar(20) = '',
        @ASSET_NO_TO nvarchar(20) = '',
        @HOLDER_CD nvarchar(4000) = '',
        @HOLDER_CD_FROM nvarchar(10) = '',  
        @HOLDER_CD_TO nvarchar(10) = '',    
        @SITE_CD nvarchar(4000) = '',
        @SITE_CD_FROM nvarchar(10) = '',
        @SITE_CD_TO nvarchar(10) = '',
        @ASSETCODE nvarchar(4000) = '',
        @ASSETCODE_FROM nvarchar(10) = null,
        @ASSETCODE_TO nvarchar(10) = null,
        @FUND_CD nvarchar(4000) = '',
        @FUND_CD_FROM nvarchar(10) = '',
        @FUND_CD_TO nvarchar(10) = '',
        @PO_NO nvarchar(4000) = '',
        @PO_NO_FROM nvarchar(15) = '',
        @PO_NO_TO nvarchar(15) = '',
        @STATUS nvarchar(4000) = '',
        @STATUS_FROM int = null,
        @STATUS_TO int = null,
        @UNITPRICE_FROM money = null,
        @UNITPRICE_TO money = null,
        @CAPITALIZED bit = Null
        -- hint recompile for new fresh execution plan
        WITH RECOMPILE
    AS
    BEGIN
        --select cast(data as nvarchar) ASSET_NO INTO #asset_nos from dbo.split(@ASSET_NO,',') 
        --    where data is not null  
        --select cast(data as nvarchar) HOLDER_CD INTO #holders from dbo.split(@HOLDER_CD,',') 
        --    where data is not null
        --select cast(data as nvarchar) SITE_CD INTO #site from dbo.split(@SITE_CD,',') 
        --    where data is not null
        --select cast(data as nvarchar) ASSETCODE INTO #assetcode from dbo.split(@ASSETCODE,',') 
        --    where data is not null
        --select cast(data as nvarchar) FUND_CD INTO #fundcode from dbo.split(@FUND_CD,',') 
        --    where data is not null
        --select cast(data as nvarchar) PO_NO INTO #pono from dbo.split(@PO_NO,',') 
        --    where data is not null
        --select cast(data as int) [STATUS] INTO #status from dbo.split(@STATUS,',') 
        --    where data is not null


        DECLARE @FIELD nvarchar(20)
        SET @FIELD = @Action

        --- FIELD Def for report From here
        SELECT 
            a.ASSET_NO
            ,a.DATE_RECEIVED as DATE_OPRT
            ,a.DATE_EFFECTIVE_REGISTERED as DATE_EFFECTIVE
            ,a.DATE_REGISTERED as DATE_MODIFIED
            ,a.USER_ID_REGIST as USER_ID_MODIFY
            ,a.DESC_1
            ,a.DESC_2
            ,a.DESC_AX
            ,a.DESC_1  
                + CASE WHEN IsNull(a.DESC_2, '') = '' THEN '' ELSE ' ' + a.DESC_2 END
                + CASE WHEN IsNull(a.DESC_AX, '') = '' THEN '' ELSE ' ' + a.DESC_AX END
                as ASSET_DESC
            ,a.MANUFACTURER
            ,a.MODEL
            ,a.SERIAL_NO
            ,a.WEIGHT
            ,a.ASSETCODE
            ,a.SITE_CD
            ,a.ROOM_CD
            ,a.BUILDING_CD
            ,a.HOLDER_CD            
            ,a.HOLDER_DESC          
            ,a.STATUS
            ,Left(a.STATUS_DESC, 10) as STATUS_DESC
            ,ac.CODE_DESCRIPTION    
            ,a.RM_DESC_1            AS ROOM_DESC
            ,a.SITE_NAME_1          AS SITE_NAME
            ,a.BUILDING_NAME    
            ,a.DATE_RECEIVED
            ,fin.DATE_INSERVICE     
            ,fin.ORIG_COST
            ,fin.QTY
            ,fin.PM_FUND_CD         as FUND_CD
            ,fin.CAPITALIZED
            ,fnd.FUND_DESC
            ,a.PM_PO_NO             as PO_NO

            -- Initial values
            ,u.ORIG_COST            AS INIT_COST
            ,ui.ASSETCODE           AS INIT_ASSETCODE
            ,uR.SITE_CD             AS INIT_SITE_CD
            ,u.ROOM_CD              AS INIT_ROOM_CD
            ,uR.BUILDING_CD         AS INIT_BUILDING_CD
            ,uR.RM_DESC_1           AS INIT_ROOM_DESC
            ,uR.BUILDING_NAME       AS INIT_BUILDING_NAME
            ,uR.SITE_NAME_1         AS INIT_SITE_NAME
            ,u.HOLDER_CD            AS INIT_HOLDER_CD
            ,uH.ORG_DESC            AS INIT_HOLDER_DESC
            ,uac.CODE_DESCRIPTION   AS INIT_CODE_DESCRIPTION

            FROM vAsset as a
            LEFT JOIN vAsset_Rgst_Info as u ON a.ASSET_ID = u.ASSET_ID
            LEFT JOIN tAssetCode    as ac ON a.ASSETCODE = ac.ASSETCODE
            LEFT JOIN tAssetFin     as fin ON a.ASSET_NO = fin.ASSET_NO
            LEFT JOIN tFunding      as fnd ON fin.PM_FUND_CD = fnd.FUND_CD
            LEFT JOIN tItemCatalog  as ui ON u.ITEM_CD = ui.ITEM_CD
            LEFT jOIN tAssetCode    as uac ON ui.ASSETCODE = uac.ASSETCODE
            LEFT JOIN vRoom         as uR ON u.ROOM_CD = uR.ROOM_CD
            LEFT JOIN tOrganization as uH ON u.HOLDER_CD = uH.ORG_CD

            WHERE 
                    ( @DATE_OPRT_FROM is null OR a.DATE_RECEIVED >= @DATE_OPRT_FROM )
                AND ( @DATE_OPRT_TO is null OR a.DATE_RECEIVED <= @DATE_OPRT_TO )
                AND ( @DATE_EFFECTIVE_FROM is null OR a.DATE_EFFECTIVE_REGISTERED >= @DATE_EFFECTIVE_FROM )
                AND ( @DATE_EFFECTIVE_TO is null OR a.DATE_EFFECTIVE_REGISTERED <= @DATE_EFFECTIVE_TO )
                AND ( @DATE_MODIFIED_FROM is null OR a.DATE_REGISTERED >= @DATE_MODIFIED_FROM )
                AND ( @DATE_MODIFIED_TO is null OR a.DATE_REGISTERED <= @DATE_MODIFIED_TO )
             --   AND ( @ASSET_NO = '' OR u.ASSET_NO in ( SELECT ASSET_NO from #asset_nos ))
                AND ( @ASSET_NO = '' OR u.ASSET_NO = dbo.inlist(@ASSET_NO,u.ASSET_NO))
                AND ( @ASSET_NO_FROM = '' OR u.ASSET_NO >= @ASSET_NO_FROM )
                AND ( @ASSET_NO_TO = '' OR u.ASSET_NO <= @ASSET_NO_TO )
            --    AND ( @HOLDER_CD = '' OR a.HOLDER_CD in ( SELECT HOLDER_CD from #holders ))
                AND ( @HOLDER_CD = '' OR a.HOLDER_CD =dbo.inlist(@HOLDER_CD,a.HOLDER_CD))
                AND ( @HOLDER_CD_FROM = '' OR a.HOLDER_CD >= @HOLDER_CD_FROM )
                AND ( @HOLDER_CD_TO = '' OR a.HOLDER_CD <= @HOLDER_CD_TO )
           --     AND ( @SITE_CD = '' OR a.SITE_CD in ( SELECT SITE_CD from #site ))
                AND ( @SITE_CD = '' OR a.SITE_CD =dbo.inlist(@SITE_CD,a.SITE_CD))
                AND ( @SITE_CD_FROM = '' OR a.SITE_CD >= @SITE_CD_FROM )
                AND ( @SITE_CD_TO = '' OR a.SITE_CD <= @SITE_CD_TO )
              --  AND ( @ASSETCODE = '' OR a.ASSETCODE in ( SELECT ASSETCODE from #assetcode ))
                AND ( @ASSETCODE = '' OR a.ASSETCODE =dbo.inlist(@ASSETCODE,a.ASSETCODE))
                AND ( @ASSETCODE_FROM is null OR a.ASSETCODE >= @ASSETCODE_FROM )
                AND ( @ASSETCODE_TO is null OR a.ASSETCODE <= @ASSETCODE_TO )
                -- PM_FUND_CD qualified by a.PM_FUND_CD
                --AND ( @FUND_CD = '' OR PM_FUND_CD in ( SELECT FUND_CD from #fundcode ))
                AND ( @FUND_CD = '' OR a.PM_FUND_CD  =dbo.inlist(@FUND_CD,a.PM_FUND_CD))

                AND ( @FUND_CD_FROM = '' OR fin.PM_FUND_CD >= @FUND_CD_FROM )
                AND ( @FUND_CD_TO = '' OR fin.PM_FUND_CD <= @FUND_CD_TO )

              --  AND ( @PO_NO = '' OR a.PM_PO_NO in ( SELECT PO_NO from #pono ))
                AND ( @PO_NO = '' OR a.PM_PO_NO =dbo.inlist(@PO_NO,a.PM_PO_NO))
                AND ( @PO_NO_FROM = '' OR a.PM_PO_NO >= @PO_NO_FROM )
                AND ( @PO_NO_TO = '' OR a.PM_PO_NO <= @PO_NO_TO )
             --   AND ( @STATUS = '' OR a.STATUS in ( SELECT [STATUS] from #status ))
                AND ( @STATUS = '' OR a.STATUS =dbo.inlist(@STATUS,a.STATUS))
                AND ( @STATUS_FROM is null OR a.STATUS >= @STATUS_FROM )
                AND ( @STATUS_TO is null OR a.STATUS <= @STATUS_TO )
                AND ( @UNITPRICE_FROM is null OR fin.UNIT_COST >= @UNITPRICE_FROM ) 
                AND ( @UNITPRICE_TO is null OR fin.UNIT_COST <= @UNITPRICE_TO )
                AND ( @CAPITALIZED is null OR  fin.CAPITALIZED = @CAPITALIZED )
    END -- proc
    go

Update:

Based on the comments that "What you have described will not work in a stored procedure because the parameter values are not known ahead of time."

The parameter values are really known a head of time,because they are received in the stored procedure header. The scalar function is executed in time of call and return value or null based on the passed parameters of the SP.

for example: The snippet code

    AND ( @ASSETCODE = '' OR a.ASSETCODE =dbo.inlist(@ASSETCODE,a.ASSETCODE))

@ASSETCODE is a parameter value (delimited list ) a.ASSETCODE is the value of the current row so, this expression is evaluated in time and return true/false The same for the other six lists used in OP

The following test case simulate the use of the scalar function {inlist} as used in my answer:

    create procedure test_delimeted_list (
    @list1 nvarchar(100)
    )
    as
    begin
    select * from products p
    where p.ProductID = dbo.[inList](@list1,p.productid)
    end
    go

Test1: list has a value

     exec test_delimeted_list '2,500,610'   -- 2  is existing

The return data from northwind database :

   ProductID    ProductName SupplierID  CategoryID  QuantityPerUnit UnitPrice   UnitsInStock    UnitsOnOrder    ReorderLevel    Discontinued
   2    Chang   1   1   24 - 12 oz bottles  19.00   17  40  25  0

Test2: list is null

    exec test_delimeted_list  null

The return data is null

I hope the OP test my code and provide comments for something missing or not working.