laravel sql server stored procedure output

Question

I have already created a SQL Store Produced like this:

USE [xxx]
GO
/****** Object:  StoredProcedure [dbo].[ResultsByRegionAndDate]    Script Date: 02/25/2016 14:29:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ResultsByRegionAndDate]
    @Region VARCHAR(10),
    @DayD DATETIME,
    @DaySpin DATETIME OUTPUT
AS
BEGIN
    /* logic here */
END

And now, in my Laravel project, althought I've tried many way to get output on stored produce but still fail.

$region = 'BAC';
$ngay = '2016-02-25';

$pdo = DB::connection()->getPdo();
$stmt = $pdo->prepare('DECLARE @DaySpin datetime EXEC dbo.ResultsByRegionAndDate ?, ?, @DaySpin');
$stmt->bindParam(1, $region);
$stmt->bindParam(2, $ngay);
$stmt->bindParam(3, $out);
$stmt->execute();
$search = array();
do {
    $search = $stmt->fetchAll(PDO::FETCH_ASSOC);
} while ($stmt->nextRowset());

return $out; // to see the result

When code is running, $out is empty, I don't know how to get output from stored product.

Are there any way to get output without modify stored produce?

Please help

Answer 1

After few hours of working, I've found the solutions for this case, if anyone has same problem like me might use this to resolve it.

Problem here is I was wrong about the way of reading output param, here is the right way.

$out = '';
$pdo = DB::connection()->getPdo();
$stmt = $pdo->prepare('DECLARE @NgayQuay datetime; EXEC dbo.ResultsByRegionAndDate ?,?, @NgayQuay OUTPUT');
$stmt->bindParam(1, $region);
$stmt->bindParam(2, $ngay);
$stmt->execute();
$stmt->bindColumn(3, $out);
$x = $stmt->fetchAll();

return $out; //that's output

Working key is $stmt->bindColumn(3, $out); hope this help someone.

Happy coding.

Answer 2

Replace $stmt = $pdo->prepare('DECLARE @DaySpin datetime EXEC dbo.ResultsByRegionAndDate ?, ?, @DaySpin OUTPUT');

If you want sql to return a value to an output parameter you have to specify OUTPUT at the end of the parameter.