Ad

Laravel Sql Server Stored Procedure Output

- 1 answer

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

Ad

Answer

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.

Ad
source: stackoverflow.com
Ad