Best Practices Q

[Member 4501940]

"When inserting or updating via proc that it is a good practice to return the new or updated rec using output parameters." Opinions - one way or the other - appreciated. Thanks

[DarrenShultz]

It really depends on how it is being used, and I personally use both techniques. I can't say I've read much on the benefits/pitfalls of each approach, but within T-SQL, I prefer the OUTPUT param simply to favor variable assignment rather than having a result set returned. If the stored procedure is being called from .NET, I usually SELECT out the record ID, and utilize the ExecuteScalar method of SqlCommand. The T-SQL usage doesn't really favor one over the other (in terms of keystrokes):

EXECUTE StoredProc @Param1, @Param2, @RecordID OUTPUT

SET @RecordID = EXECUTE StoredProc @Param1, @Param2

OUTPUT Parameter: If stored procedure is being called from within T-SQL. If the new record ID is not always used by the caller. SELECT Record ID: If stored procedure is being called from .NET (use SqlCommmand.ExecuteScalar). If the new record ID is always used by the caller. If you do choose to take the OUTPUT parameter route, I would suggest you are careful about how you assign the new record ID to that OUTPUT parameter. If the stored proc is being called within a loop, you'll want to make sure the previous value does not remain in the output parameter (in the event that a record is not actually affected). You may also want to set the default value for that OUTPUT param as NULL so it is optional (in the event the caller does not require the record ID).

[Lost User]

Member 4501940 wrote:

that it is a good practice to return the new or updated rec using output parameters.

Having multiple paths to Rome doesn't mean that one traderoute is the "bestest". Some paths are optimized for heavy traffic, while others are only accessible by horse. It's a good practice to let the programmer decide what solution would fit best into the problem at hand. It might be quite acceptable to return the identity using a simple select statement :)

I are Troll :suss: