php PDO fetch zero results for SQL Server-CodePudding

I have the following bit of code that fetches results from a query.

$sql =
"
    SELECT 1 AS bar
    FROM outsource.prodgroup_division
    WHERE
    (
        CASE
            WHEN :division = 'ALL'                       THEN 1
            WHEN prodgroup_division.division = :division THEN 1
                                                         ELSE 0
        END
    ) = 1
";
$stmt = $dw_db->prepare($sql);
$stmt->bindParam(':division', $division, 2); // 2 for strings
$stmt->execute();
$rows = $stmt->fetchAll();
$stmt->closeCursor();

My division parameter is set to 'ALL'. When I run it without the parameters and hardcode in 'ALL' it gives me 22 rows. Why am I getting zero data in my $rows array?

CodePudding user response：

I am not sure why, maybe someone can help me understand. When I changed the syntax from named parameters to the following it worked.

$sql =
"
    SELECT 1 AS bar
    FROM outsource.prodgroup_division
    WHERE
    (
        CASE
            WHEN 'ALL'                    LIKE ? THEN 1
            WHEN prodgroup_division.division = ? THEN 1
                                                 ELSE 0
        END
    ) = 1
";
$stmt = $dw_db->prepare($sql);
$stmt->bindValue(1, $division); // 2 for strings
$stmt->bindValue(2, $division); // 2 for strings
$stmt->execute();
$rows = $stmt->fetchAll();
$stmt->closeCursor();

CodePudding user response：

I'm not sure why you got zero results rather than an error (possibly you have PDO set to an unhelpful error mode?), but I believe the problem is that bound parameters can only be used once, even if you use named parameter syntax.

You can either give each one a distinct name, and bind the same value to both:

$sql =
"
    SELECT 1 AS bar
    FROM outsource.prodgroup_division
    WHERE
    (
        CASE
            WHEN :division1 = 'ALL'                       THEN 1
            WHEN prodgroup_division.division = :division2 THEN 1
                                                          ELSE 0
        END
    ) = 1
";
$stmt = $dw_db->prepare($sql);
$stmt->bindParam(':division1', $division, \PDO::PARAM_STR);
$stmt->bindParam(':division2', $division, \PDO::PARAM_STR);

(Switching to positional parameters works for the same reason - you've bound the same value to multiple placeholders, rather than trying to reuse one.)

Or you can use the bound parameter to populate a Transact-SQL variable, and use that in the query:

$sql =
"
    DECLARE @division INT = :division

    SELECT 1 AS bar
    FROM outsource.prodgroup_division
    WHERE
    (
        CASE
            WHEN @division = 'ALL'                       THEN 1
            WHEN prodgroup_division.division = @division THEN 1
                                                         ELSE 0
        END
    ) = 1
";
$stmt = $dw_db->prepare($sql);
$stmt->bindParam(':division', $division, \PDO::PARAM_STR);

(Side note: I've used the provided constant \PDO::PARAM_STR rather than the magic number 2, as recommended in the manual.)