I used to have procedure like this:

CREATE PROCEDURE [dbo].[do_something]
    (
    @id int
    )
AS
...
...

I needed to have some return value, so I changed this to:

CREATE PROCEDURE [dbo].[do_something]
    (
    @id int,
    @closed int OUTPUT
    )
AS
...
...

But now I need to pass closed variable every time when I want to execute this procedure.

EXECUTE @RC = [dbo].[do_something] 
   @id
  ,@closed OUTPUT
GO

Is it possible to execute it without passing this variable (sometimes I dont need to analyse closed)?

In many languages you can either store return value like this:

bool result = DoSomethingAndGetResult();

or execute function without storing return value:

DoSomethingAndGetResult();

CodePudding user response：

You have two other options to return data from a stored procedure:

1. Use a Return statement. This is handled similar to an output variable in client code, but does not require you to assign the parameter.
2. SELECT the output into a result set. This can require a client system capable of checking multiple result sets if you also select other data. ADO.Net, for example, let's you do this either via a DataAdapter/DataSet or DataReader/MoveNext(), but not every library or ORM built with ADO.Net exposes the feature.

CodePudding user response：

In additional to @JoelCoehoorn's answer, you can make the output parameter optional by assigning it a default value and then you aren't required to pass it every time.

SQL Server Docs: https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/specify-parameters?view=sql-server-ver16#-specify-parameter-default-values

create proc dbo.test (@p1 int = null, @p2 int = null output)
as
begin
    set @p2 = 1;
end
go
--this works
exec dbo.test;