sql server - PHP预备语句变量与子查询绑定错误 - sql server - PHP Prepared Statement variable binding error with subquery

sql server - PHP Prepared Statement variable binding error with subquery

我有一个查询与几个子查询像这样

SELECT ...
FROM    (SELECT ...
         FROM ...
         GROUP BY ...) as speedLimitCalc INNER JOIN 
        (SELECT ...
        FROM date a INNER JOIN HOURLY_TEST b ON a.[FULL_DAY_DT] = b.DATE
        WHERE (b.DATE BETWEEN '".$date_s."' AND '".$date_e."') 
            AND HOUR BETWEEN ".$time_s." AND ".$time_e."
            AND(LKNO BETWEEN '".$lkno_s."' and '".$lkno_e."')
            AND RDNO= '".$rdno."'
            AND pub_hol IN (".$pubholquery.")
            AND school_hol IN (".$schholquery.")
            AND day_no IN (".$dayquery.")                                   
            GROUP BY RDNO, LKNO, PRESCRIBED_DIRECTION, CWAY_CODE)  as origtable ON ...
        ,(SELECT ...
          FROM [Dim_date]
          WHERE (FULL_DAY_DT BETWEEN '".$date_s."' AND '".$date_e."')
            AND pub_hol IN (".$pubholquery.")
            AND school_hol IN (".$schholquery.")
            AND day_no IN (".$dayquery.") ) as c
ORDER BY ...

我在内部查询where子句中插入变量。

我试图使用odbc_prepare和odbc_execute参数化这个查询，但是我遇到了绑定变量的问题。目前，当我使用以下

时

$result = odbc_prepare($connection, $query);
odbc_execute($result)or die(odbc_error($connection));

运行这个查询，一切正常。但是，当我尝试绑定变量时，例如

AND RDNO= ?
...
odbc_execute($result, array($rdno))or die(odbc_error($connection));

我得到以下错误信息:

PHP Warning: odbc_execute() [/phpmanual/function.odbc-execute.html]: SQL error: [Microsoft][ODBC SQL Server Driver]Invalid parameter number, SQL state S1093

我的猜测是，这是因为我在子查询中绑定了一个变量，因为这个过程在Where子句位于top Select查询中时起作用。

我想知道其他人之前是否遇到过这个问题，他们是如何解决的?由于

通过使用临时表将查询分离为多个查询来消除子查询中对参数的需求，修复了这个问题。

$query = "SELECT ...
          INTO ##avgspeedperlink
          FROM Date a INNER JOIN HOURLY_TEST ON a.[FULL_DAY_DT] = b.DATE 
          WHERE (b.DATE BETWEEN ? AND ?) 
            AND HOUR BETWEEN ? AND ?
            AND(LKNO BETWEEN ? and ?)
            AND RDNO= ?
            AND pub_hol IN (".$pubholquery.")
            AND school_hol IN (".$schholquery.")
            AND day_no IN (?,?,?,?,?,?,?) 
          GROUP BY RDNO, LKNO, PRESCRIBED_DIRECTION, CWAY_CODE";
$result = odbc_prepare($connection, $query);
odbc_execute($result, array($date_s,$date_e,$time_s,$time_e,$lkno_s,$lkno_e,$rdno,$daysanitised[0],$daysanitised[1],$daysanitised[2],$daysanitised[3],$daysanitised[4],$daysanitised[5],$daysanitised[6]))or die(odbc_error($connection));
$query = "SELECT ...
          INTO ##daysinperiod
          FROM [RISSxplr].[dbo].[Dim_date]
          WHERE (FULL_DAY_DT BETWEEN ? AND ?)
            AND pub_hol IN (".$pubholquery.")
            AND school_hol IN (".$schholquery.")
            AND day_no IN (?,?,?,?,?,?,?)";
$result = odbc_prepare($connection, $query);
odbc_execute($result, array($date_s,$date_e,$daysanitised[0],$daysanitised[1],$daysanitised[2],$daysanitised[3],$daysanitised[4],$daysanitised[5],$daysanitised[6]))or die(odbc_error($connection));
$query = "SELECT ...
          FROM ##avgspeedperlink, ##daysinperiod
          ORDER BY LKNO, OUTBOUND
drop table ##avgspeedperlink
drop table ##daysinperiod";

请注意，我必须使用双##来创建临时表(单个#表示表对于查询是本地的，##表示临时表对于多个查询是全局的)。