MSSQL Stored Procedures - Returning id after insert

replied 9 years ago

Hello! I'm on the same boat right now!

I'm no expert, but i guess you need a SP with OUT parameter.

You should alter your sp, and add a out parameter.

getjop
(@id INT,                       
@design VARCHAR(200)  OUT        
)
AS
BEGIN
--insert data here
SET @design = inserted_id  -- set your OUT parameter
END

When you run your SP from laravel,

declare @design as varchar(200) ; exec getjop 2 , @design output ; select @design

This above code works in SQL management studio...

What i'm having a hard time is to retrieve the OUT parameter back to php!

Did you manage to work this out?

0

iboinas

replied 9 years ago

Please check this!! Got me working!!

http://laravel.io/forum/04-03-2014-stored-procedure-out-value-error

But my SP working, needed the declare before and select afterwards...

If you need some example code, let me know!

0

prettyhotprogrammer

replied 9 years ago

Solution

I never posted the solution I figured out, so here it goes if anyone else needs it. This example shows you how to return the newly created id.

Example MS Stored Procedure

CREATE PROCEDURE ReturnIdExample
(
         @paramOne int
        ,@paramTwo nvarchar(255)
)
AS
SET NOCOUNT ON; --IMPORTANT!
BEGIN
INSERT INTO [Table]
(
         [ColumnNameA]
        ,[ColumnNameB]
) VALUES (
         @paramOne
        ,@paramTwo
)
END

-- Grab the id that was just created
DECLARE @ObjectID int;
SET @ObjectID = SCOPE_IDENTITY();

-- Select the id to return it back to laravel
SELECT@ObjectID AS ObjectID;

Now in your controller $submit = DB::select("EXEC ReturnIdExample ?,?", array( $paramOne ,$paramTwo ) );

return $submit[0]->ObjectId;

This is how I was able to solve my problem.

Thanks, PHP

Last updated 9 years ago.

me2ulab liked this reply

1

Moderators