设置NOCOUNT在ubuntu下不工作

Question

我正在使用mssql服务器和yii framework，我已经创建了一个stored-procedure，请参阅下面的代码。

 //Call Store procedure to get data
 $sql = "EXECUTE IESReportData @assessmentId=:assessmentId, @queId=:queId,@instanceId=:instanceId";
 //set database connection and start the yii query builder to be executed.
 $connection = Yii::app()->db;
 $command = $connection->createCommand($sql);
 $command->bindValue(":assessmentId", $assessmentId);
 $command->bindValue(":queId", "");
 $command->bindValue(":instanceId", "$instanceId");
 $Reportresults = $command->queryAll();

这在ubuntu环境下工作很好，但在windows环境下会出现错误。

Fatal error: Uncaught exception 'CDbException' with message 'CDbCommand failed to execute the SQL statement: SQLSTATE[IMSSP]: The active result for the query contains no fields.

通过一些研发，我发现我们需要在上设置NOCOUNT，所以我更改了下面的语句

$sql = "SET NOCOUNT ON EXECUTE IESReportData @assessmentId=:assessmentId, @queId=:queId,@instanceId=:instanceId";

这在window中很好，但是它在ubuntu环境下提供了空结果。

请帮帮我。

Answer 1

下面是在存储过程中设置nocount的示例。

​

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END`enter code here`
GO

​