How to execute such query in MSSQL Server from Laravel?

Question

SQL Server

Good day! I ran into such a problem:
There is a remote SQL Server, like 2012 (but this is not accurate), in which there is a stored procedure that must be used for authorization in a Laravel application, an example query:

declare @Entity int, @CompName varchar(250)
exec [foo].[prcBar] 'userlogin', 'userpass', @Entity output, @CompName output
select @Entity, @CompName

In response, I get 2 fields - a certain id and a name. Accordingly, when executed in the console, I get all this data. But when sending from lara, I either get an empty array, or an error that variables are not declared.
How I tried:
1.

$test = DB::transaction(function(){
        DB::statement('declare @Entity int, @CompName varchar(250)');
        DB::statement('exec [foo].[prcBar] ?, ?, @Entity output, @CompName output',  ['userlogin', 'userpass']);
        return DB::select('select @Entity, @CompName');
});

I am getting an error:

SQLSTATE[HY000]: General error: 20018 Must declare the scalar variable "@Entity". [20018] (severity 15) [exec [foo].[prcBar] 'userlogin', 'userpass', @Entity output, @CompName output] (SQL: exec [foo].[prcBar] userlogin, userpass, @Entity output, @CompName output)",

Without the transaction, the result is the same
2.

$test = DB::select(DB::raw('declare @Entity int, @CompName varchar(250)
                exec [foo].[prcBar] ?, ?, @Entity output, @CompName output
                select @Entity, @CompName'), ['userlogin', 'userpass']);

I get an empty array
3.

$db = DB::connection()->getPdo();
$stmt = $db->prepare("
            declare @Entity int, @CompName varchar(250)
            exec [foo].[prcBar] 'userlogin', 'userpass', @Entity output, @CompName output
            select @Entity, @CompName
");
$stmt->execute();
$test = $stmt->fetchAll(\PDO::FETCH_CLASS, 'stdClass');

I get an empty array
. These are just the main options that I tried. There were also all sorts of combinations with DB:raw (), DB:: table ()-> select () and completely beaten off options by the "method" at random, which I don’t remember anymore (they didn’t work anyway).
The only option that I have found so far is to change the storage itself by adding the select I need to it, but so far this is not possible. The code of the procedure itself, unfortunately, is not available to me.
Actually a question - how it is possible to execute such request? Thanks in advance.

Answer 1

In general, after several hours of kicking the sqlsrv driver built into laravel, I came to the conclusion that the problem is in the driver itself. I went poking around and looking for solutions to work through odbc and found the following package: techscope/laravel-sqlserver
When installing by composer, an error occurs, they say ext-odbc is not installed, installed with the command (ubuntu)
Further, my problem remained, but ext-odbc was definitely installed, because. displayed in the list of connected. I wrote off the package for bagul and installed it with the command
After I corrected the sqlsrv section in config/database.php by adding

/* ... */
'sqlsrv' => [
...
    'odbc_driver'   => '{ODBC Driver 17 for SQL Server}',
    'TrustServerCertificate' => 'yes',
...
],
/* ... */

We look at the value for `odbc_driver` with the command
What we put in square brackets in the config into curly ones.
Well, don't forget to add the provider to donfig/app.php.
After the manipulations, it was possible to execute the entire query in DB::select():

test = DB::select("declare @Entity int, @CompName varchar(250)
    exec [foo].[prcBar] 'testlogin', 'testpass', @Entity output, @CompName output
    select @Entity as entity, @CompName as comp_name
");

An important point - you must explicitly specify the value `@Var as col_name` in the selection, otherwise the array will have 1 element with an empty key and the last value