SQL Server Why its necessary to pass output variable every time

snz8szmq 于 2023-11-16 发布在其他

关注(0)|答案(2)|浏览(92)

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();

sql-server

来源：https://stackoverflow.com/questions/77417203/why-its-necessary-to-pass-output-variable-every-time

2条答案

按热度按时间

mo49yndu1#

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

Use a Return statement. This is handled similar to an output variable in client code, but does not require you to assign the parameter.
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.

赞(0）回复(0）举报 2023-11-16

nuypyhwy2#

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;

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server Why its necessary to pass output variable every time

2条答案

相关问题

热门标签

最新问答