Getting output parameter value from Microsoft SQL Server stored procedure using Laravel

Question

I am trying to insert a record into the Microsoft SQL Server database via stored procedure using Laravel and get the value from an output parameter. My record is inserted but did not get @RetValue.

For this, I tried

DB::select("DECLARE @RetValue INT; SELECT @RetValue as 'return'; EXEC AddDistrict 'somevalue', 1, @RetValue OUTPUT; SELECT @RetValue as 'abc';");
DB::select(DB::Raw("Declare @RetValue int EXEC AddDistrict 'somevalue', '1', @RetValue OUTPUT"),'select @RetValue as abc');
DB::select(DB::statement('CALL AddDistrict("DName"="asdfasdf", "PID"=1,"RetValue"="" )').'@RetValue as RetValue');
DB::select(" EXEC AddDistrict ?, ?", array( 'some_value',1));
DB::select(DB::raw("exec AddDistrict @DName = 'some_value', @PID = 1, @RetValue=''"));
DB::select('CALL AddDistrict(?, ?, ?)',
     array(
        'DName' => $request->DistrictName,
        'PID' => $request->province,
         'RetValue' => ''
        ));
DB::select('exec AddDistrict(?,?)',"some_value',1);

and many others, but I did not get the @RetValue. Mostly I get an empty array like this [].

My stored procedure looks like this:

CREATE PROCEDURE [dbo].[AddDistrict]
    @DName nvarchar(50),
    @PID int,
    @RetValue int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT * FROM District WHERE District_Name = @DName)
    BEGIN
        INSERT INTO [dbo].District ([District_Name], ProvienceID)
        VALUES (@DName, @PID)

        SET @RetValue = 1;
    END
    ELSE
    BEGIN
        SET @RetValue = -1;
    END
END

If the record is inserted, I want to get 1, if did get not inserted, then I want to get -1 as described in the stored procedure.

@Larnu Yes, of course, I am talking about the output parameter. sorry for not conveying my message properly. — atta afridi
– atta afridi, Commented Aug 17, 2022 at 15:14
Remove all CALL statements, those are not the syntax of SQL Server. I then used DB::statement to launch a procedure. Mine was on MySQL and not on SQLS. You may want to see the documentation for this — noxter
– noxter, Commented Aug 31, 2022 at 8:23

atta afridi · Accepted Answer · 2022-12-12 14:25:35Z

0

I found a solution to that. Maybe it's not the proper way. But my code is now running smoothly after the below changes. Just remove this variable @RetValue int output and replace set @RetValue=1; to SELECT 1;

answered Dec 12, 2022 at 14:25

atta afridi

965 bronze badges

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

Collectives™ on Stack Overflow

Getting output parameter value from Microsoft SQL Server stored procedure using Laravel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related