I have SQL that looks like this, which works fine in SSMS:

    DECLARE @pPart  VARCHAR(100)    =   '00039',    
            @pColor VARCHAR(100)    =   '01816'         

    SET     @pPart   = ISNULL(@pPart,'-1')          
    SET     @pColor  = ISNULL(@pColor,'-1')         

    SELECT  *
    FROM    myTable
    WHERE   (PartID  IN (SELECT (@pPart))  OR @pPart  = '-1')
    AND     (ColorID IN (SELECT (@pColor)) OR @pColor = '-1')

If I change either value (or both values) in the DECLARE from a value to NULL, the code returns the results I expect. I'm just not sure how to make this work within Power Query. If both cells are populated with values, the query works. If I delete the value from either cell, it's just returning a list of all the tables in my database (weird...).

I created a named range called "GetValues" that covers B2:B3 and looks like this:

In the Advanced Editor of Power Query, my query looks like this:

let
    Source  = Excel.CurrentWorkbook(){[Name="GetValues"]}[Content],
    pPart   = Source{1}[Column1],
    pColor  = Source{2}[Column1],
    Query   = "

    DECLARE @pPart  VARCHAR(100)    =   '"& pPart &"',  
            @pColor VARCHAR(100)    =   '"& pColor &"'          

    SET     @pPart  = ISNULL(@pPart,'-1')           
    SET     @pColor = ISNULL(@pColor,'-1')          

    SELECT  *
    FROM    myTable
    WHERE   (PartID  IN (SELECT (@pPart))  OR @pPart  = '-1')
    AND     (ColorID IN (SELECT (@pColor)) OR @pColor = '-1')
    ",

    Target = Sql.Database("myServer", "myDatabase", [Query=Query])
in
    Target

I tried changing my SET lines, which also works in SSMS if I change a value to '' or ' ' but still returns a list of all tables in my DB in Power Query. I'm trimming the strings in case the user put a space in the cells instead of leaving them blank:

    IF LTRIM(RTRIM(@pPart))  = '' SET @pPart  = '-1' 
    IF LTRIM(RTRIM(@pColor)) = '' SET @pColor = '-1'        

So how do I pass a blank cell as NULL or at least as '' ?

EDIT: I also tried stripping my query down to the bare bones to see what parameters are being passed. This query successfully returns the value in B2.

let
    Source  = Excel.CurrentWorkbook(){[Name="GetValues"]}[Content],
    pPart   = Source{1}[Column1],
    Query   = "

    DECLARE @pPart  VARCHAR(100)    =   '"& pPart &"'   
    SELECT  @pPart

    ",

    Target = Sql.Database("myServer", "myDatabase", [Query=Query])
in
    Target

Now I want to see what happens when I include the blank cell B3:

let
    Source  = Excel.CurrentWorkbook(){[Name="GetValues"]}[Content],
    pPart   = Source{1}[Column1],
    pColor  = Source{2}[Column1],
    Query   = "

    DECLARE @pPart  VARCHAR(100)    =   '"& pPart &"',  
            @pColor VARCHAR(100)    =   '"& pColor &"'          
    SELECT  @pPart, @pColor

    ",

    Target = Sql.Database("myServer", "myDatabase", [Query=Query])
in
    Target

This query also returned a list of all the tables in my database.

I also tried changing

in
    Target

to

in
    pColor 

and the query preview shows null in all lowercase, which makes me think it's indeed returning a null value. I just can't figure out how to make this work.

CodePudding user response：

If the goal is to treat blank cell values as a wildcard then this will work: first remove the SET statements then use this sql:

    SELECT  *
    FROM    myTable
    WHERE   (PartID  = @pPart OR @pPart  is null OR TRIM(@pPart) ='')
    AND     (ColorID = @pColor OR @pColor is null OR TRIM(@pColor) ='')

You would get:

• Alll rows only if both variables are blank.
• No rows if @pPart is not null/blank and has no matches
• No rows if @pColor is not null/blank and has no matches

You could add this condition to prevent returning any rows when both inputs are blank/ null:

AND ( ISNULL(TRIM(pPart), '') <> '' OR  ISNULL(TRIM(pColor), '') <> '')

CodePudding user response：

To get this to work, I had to address the null values before the query started. For whatever reason, passing pPart or pColor as NULL would just return a list of tables and didn't actually pass the NULL value. Here's what seems to be working:

let
    Source  = Excel.CurrentWorkbook(){[Name="GetValues"]}[Content],
    pPart   = if Source{1}[Column1] = null then "-1" else Source{1}[Column1],
    pColor  = if Source{2}[Column1] = null then "-1" else Source{2}[Column1],
    Query   = "

    DECLARE @pPart  VARCHAR(100)    =   '"& pPart &"',  
            @pColor VARCHAR(100)    =   '"& pColor &"'          

    SELECT  *
    FROM    myTable
    WHERE   (PartID= '" & pPart & "' OR " & pPart & " = '-1')
    AND     (ColorID = '" & pColor & "' OR " & pColor & " = '-1')
    ",

    Target = Sql.Database("myServer", "myDatabase", [Query=Query])
in
    Target