我正在使用PHP向Sql Server发送查询。问题是，如果"SELECT 1 AS updateResults"语句是在UPDATE语句之后完成的，那么它就不起作用。一个资源被返回给PHP，但它没有任何行。
以下是我的查询的简化版本：
--SELECT 1 AS updateResults --if this is done before the UPDATE, a row gets returned
UPDATE theTable SET theValue = 'x' WHERE theRow = '5'
SELECT 1 AS updateResults --if this is done after the UPDATE, a row doesn't get returned
我的UPDATE查询是有效的，并且执行它应该执行的操作。
我还尝试了"SELECT‘updateResults’=1"格式，并尝试将；在每一句话的结尾，都没有什么区别。
有什么想法吗？
以下是PHP代码：
$updateSQL = "BEGIN TRANSACTION
DECLARE @result1 int
DECLARE @result2 int
--SELECT 1 AS updateResults --if this is here, the row gets returned
UPDATE theTable
SET endDate = '" . $endDate . "'
WHERE permitYear = '" . $permitYear . "'
UPDATE theTable
SET startDate = '" . $startDate . "'
WHERE permitYear = '" . $nextYear . "'
--Test to make sure both records were saved
SELECT @result1 = permitYear
FROM theTable
WHERE permitYear = '" . $permitYear . "'
AND endDate = '" . $endDate . "'
SELECT @result2 = permitYear
FROM theTable
WHERE permitYear = '" . $nextYear . "'
AND startDate = '" . $startDate . "'
if ((@result1 > 0) AND (@result2 > 0))
BEGIN
COMMIT TRANSACTION
SELECT 1 AS updateResults
END
ELSE
BEGIN
ROLLBACK TRANSACTION
SELECT 0 AS updateResults
END";
$updateRS = sqlsrv_query($con, $updateSQL);
if (!is_resource($updateRS)) {
//There was a problem. A resource wasn't returned. It never fails here
exit;
}
if (!sqlsrv_has_rows($updateRS)) {
echo ("fail for no rows returned");
exit;
}
$updateARR = sqlsrv_fetch_array($updateRS);
//It makes it here if the SELECT is done before the UPDATE
if ($updateARR['updateResults'] == '1') {
//success
}
else {
//save problem
}
附加信息：如果我去掉2个UPDATE语句，它将按预期运行并返回updateResults行。
您应该像这样查询选择
SELECT * FROM table1 WHERE 'updateResults' = 1
我最终在两个SQL语句中完成了这项工作，而不是一个。以下是我所做的：
我从$updateSQL中取出SELECT x AS updateResults行，然后：
$updateSQL = ...(same as the question, without the SELECT x AS updateResults lines)...
sqlsrv_query($con, $updateSQL);
$testSQL = "
DECLARE @result1 int
DECLARE @result2 int
SELECT @result1 = permitYear
FROM theTable
WHERE permitYear = '" . $permitYear . "'
AND endDate = '" . $endDate . "'
SELECT @result2 = permitYear
FROM Transportation.dbo.PermitDate
WHERE permitYear = '" . $nextYear . "'
AND startDate = '" . $startDate . "'
if ((@result1 > 0) AND (@result2 > 0))
BEGIN
SELECT 1 AS updateResults
END
ELSE
BEGIN
SELECT 0 AS updateResults
END
";
$testRS = sqlsrv_query($con, $testSQL);
if (!is_resource($testRS)) {
//There was a problem. A resource wasn't returned
}
if (!sqlsrv_has_rows($testRS)) {
//fail for no rows
}
$testARR = sqlsrv_fetch_array($testRS);
if ($testARR['updateResults'] == '1') {
//success
}
else {
//fail - the changes were not saved
}
当SELECT和UPDATE语句是事务的一部分时，您是否尝试颠倒它们的顺序？如果没有，我会尝试：
代替：
COMMIT TRANSACTION
SELECT 1 AS updateResults
尝试：
SELECT 1 AS updateResults
COMMIT TRANSACTION
我没有"SET NOCOUNT ON"，所以在SELECT结果之前会返回诸如"（1行受影响）"之类的SQL消息。我的代码应该是：
$updateSQL = "SET NOCOUNT ON
BEGIN TRANSACTION
...
通过这种方式，SQL消息将被抑制。
顺便说一句，在我解决了这个问题后，我注意到了以下内容：尽管我没有为此使用存储过程，但我注意到，当您在SQL Server Management Studio中创建存储过程时，它甚至会为您在模板中放入以下内容：
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;