Is it safe to Alter an existing stored procedure to use output parameters?

Question

Can changing an existing parameter of a stored procedure to an output parameter have any impact on existing code?

For context, I have stored procedure which accepts and then modifies an parameter, returning the modified parameter by selecting it. C# Callers receive the returned parameter via SqlCommand.ExecuteReader.

The stored procedure looks something like this:

CREATE procedure UpsertData
    @objectid int, --This line would change
    ...
as

if not exists (select objectid from mytable where objectid = @objectid)
begin
    insert into mytable (...) values (...)
    set @objectid = (select scope_identity() as int)
end
else
begin
    update mytable
    set ...
    where objectid=@objectid
end
select @objectid

I now intend to call this stored procedure within other stored procedures. Using INSERT-EXEC would allow me to avoid modifying UpsertData, which is used in several places.

However, it strikes me as cleaner to replace @objectid int, with @objectid int output,. I am unsure if this is safe; the stored procedure is called in many places, so I fear it might break in some unanticipated fashion. Is this a legitimate concern?

Answer 1

It seems safe.

From https://technet.microsoft.com/en-us/library/ms187004%28v=sql.105%29.aspx :

You can execute a stored procedure with OUTPUT parameters and not specify OUTPUT when executing the stored procedure. No error is returned, but you cannot use the output value in the calling program.