Error : The active result for the query contains no fields

Question

I have a stored procedure to be called from Laravel. It takes 4 inputs and returns a string data.

My code:

$getStatus = DB::select(
   "EXEC rsp_GetStatus '" . 
   $flag . 
   "', $id, $this->userId, 
   '" . $country . "', 
   '' "
);

But I'm getting the error:

"The active result for the query contains no fields".

Tried using: SET NOCOUNT ON

 $status = DB::select("SET NOCOUNT ON; EXEC rsp_GetStatus ?, ?, ?, ?, ?", 
    [
     $flag, 
     $id,
     $this->userId,
     $country,
     '']);

Still the error .

This stored procedure checks the id and returns a string value:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[rsp_GetStatus]  
    @param_flag varchar(25),
    @param_id bigint,  
    @param_userid int, 
    @param_countryoforigin varchar(25),
    @param_suggestedFlag varchar(25) OUTPUT
AS  
BEGIN
    SELECT @param_suggestedFlag = (SELECT ...)
END

Any help would be much appreciated.

How did you try to use `SET NOCOUNT ON;`? It needs to be the very first statement inside the stored procedure before any delete, insert, update or select statements. — AlwaysLearning, Jun 19 '23 at 06:25
Your real problem seems to be getting Laravel to consume an output parameter from a stored procedure - but your output parameter, `@param_suggestedIMU`, is not declared anywhere. — AlwaysLearning, Jun 19 '23 at 06:38
Can you help me with the syntax to pass an output parameter here. I tried passing a blank value in its place. — tyro, Jun 19 '23 at 06:43
@Zhorov This stored procedure is already created by client and is used elsewhere. I m regenerating the C# code in PHP Laravel. — tyro, Jun 19 '23 at 07:43
Does this answer your question? [Laravel Model SQL Server: Get Output Parameters from Stored Procedure](https://stackoverflow.com/questions/52070741/laravel-model-sql-server-get-output-parameters-from-stored-procedure) — siggemannen, Jun 19 '23 at 11:40
@siggemannen, I think the example demonstrates how to get the result from execution of the stored procedure, not the value of the output parameter. I have limited experience with Laravel, but a possible approach is to use the PDO instance of the DB, execute the statement and fetch all result set. After that, I hope, the value of any output parameters will be available. A simillar [Q&A](https://stackoverflow.com/questions/50644625/laravel-multi-result-set-query-builder/50644839#50644839). — Zhorov, Jun 19 '23 at 11:49
@tyro, does using `DB::staement` instead of `DB::select` help? — Zhorov, Jun 19 '23 at 13:27

tyro · Answer 1 · 2023-06-28T03:32:12.517

As suggested by @AlwaysLearning and @Zhorov declaring the Output variable and fetching it worked. Here is the solution:

    $tsql  = "DECLARE @param_suggestedFlag int; SET NOCOUNT ON ; ";
    $tsql .= "EXEC rsp_GetStatus '$flag', $id, @param_suggestedFlag OUTPUT;";
    $tsql .= "SELECT @param_suggestedFlag AS suggestedFlag;";
    $flagData =  DB::select($tsql);
    $suggestedFlag = $flagData[0]->suggestedFlag;

Error : The active result for the query contains no fields

1 Answers1