NULL values in multivalue parameter

Question

This stored procedure beneath fills up my Projectphase parameter. So as you can see, the user first has to select @PurchaseOrder, which will then fill up the Projectphase Parameter.

CREATE PROCEDURE [dbo].[USP_GetProjectPhase] 
     @PurchaseOrder INT
AS
    SELECT       
        pp.ProjectPhaseID, pp.Phase  
    FROM        
        ProjectPhase pp
    WHERE       
        @PurchaseOrder = pp.PurchaseOrderId

Now when the user selects a PurchaseOrder that indeed has Projectphases, everything goes well. The issue situates itself in the purchaseorders that don't have a ProjectPhase.

The query that is used for my dataset shown on the report has the following line in the WHERE clause. It's a multivalue parameter cause the user needs to be able to select multiple Projectphases.

WHERE 
    reg.ProjectPhaseId IN (SELECT Value 
                           FROM fnLocal_CmnParseList(@Phase,','))

I've tried UNIONS with NULL, NULL. I've tried stuff with ISNULL but I can't seem to be getting the query to execute when @ProjectPhase is NULL.

Some help would be greatly appreciated cause I've been cracking my head on this for too long now. Thanks

Answer 1

Finally found the answer:

[dbo].[USP_GetProjectPhase] @PurchaseOrder INT
AS

SELECT      -1 AS 'ProjectPhaseID'
            ,'No Filter' AS 'Phase'
UNION
SELECT       pp.ProjectPhaseID
            ,pp.Phase  

FROM        ProjectPhase pp
WHERE       @PurchaseOrder = pp.PurchaseOrderId 

In my query I changed the WHERE clause to:

WHERE (reg.ProjectPhaseId IN (SELECT Value FROM fnLocal_CmnParseList(@Phase,',')) OR @Phase = '-1')