Thread: Out parameter Vs Select and ExecuteScalar() in Stored Procedure

I was just wondering if there is any advantage of using an out parameter over just using a select statement after inserting a record and returning an autonumber PK field inside a Stored Procedure.

So, would there be any difference between doing this:-

Code:

CREATE PROCEDURE AddSomething
(
name VARCHAR(100),
OUT id INT
)

BEGIN

INSERT INTO tbl
(Name)
VALUES
(
name
)
;

SELECT LAST_INSERT_ID() INTO id;

END

And then retrieving the value in .NET using a parameter object or doing this:-

Code:

CREATE PROCEDURE AddSomething
(
name VARCHAR(100)
)

BEGIN

INSERT INTO tbl
(Name)
VALUES
(
name
)
;

SELECT LAST_INSERT_ID();

END

And just using command.ExecuteScalar(); to retrieve the value.

Does one have an advantage over the other?

I don't see any advantage of one over the other, and generally use the latter. I'd save OUT parameters for when a store procedure would need to return a result set AND some other value(s) -- or when a sproc needs to return a value to another sproc.

Originally Posted by Wolffy

I don't see any advantage of one over the other, and generally use the latter. I'd save OUT parameters for when a store procedure would need to return a result set AND some other value(s) -- or when a sproc needs to return a value to another sproc.

Cool. That makes sense...

Tho, I suppose, using an OUT parameter would be just a tiny bit more efficient since you then use ExecNonQuery and don't seed to setup a result set.

Originally Posted by Wolffy

Tho, I suppose, using an OUT parameter would be just a tiny bit more efficient since you then use ExecNonQuery and don't seed to setup a result set.

You've always got to complicate things, haven't you Wolffy!!