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

Viet Nguyen · Accepted Answer · 2016-02-25 14:44:20Z

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.

answered Feb 25, 2016 at 14:44

Viet Nguyen

2,3792 gold badges27 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

moses toh Over a year ago

Maybe you can help me. Look at this : stackoverflow.com/questions/51838922/…

CiucaS · Accepted Answer · 2016-02-25 10:52:42Z

0

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.

answered Feb 25, 2016 at 10:52

CiucaS

2,1486 gold badges40 silver badges69 bronze badges

3 Comments

Viet Nguyen Over a year ago

I do that too but I don't know how to get @DaySpin value

CiucaS Over a year ago

I really can't help you with laravel, I was poiting out the SQL mistake. Maybe look into this stackoverflow.com/questions/22517903/… ?

Viet Nguyen Over a year ago

Thanks for what you have done for me, I've found the solutions for this problem.

Collectives™ on Stack Overflow

laravel sql server stored procedure output

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related