How to return value from stored procedure without output parameters

Question

Here's psuedo-code for the stored procedure I currently use,

CREATE PROC uspFoo
(
@id int,
@type nvarchar(255),
@status bit output
)
AS
IF ....
SET @status=1
ELSE
SET @status=0
GO

When executing this stored procedure, I am forced to pass an output parameter that will store its return value,

DECLARE @id int, @type nvarchar(255), @status bit
SET @id=..
SET @type=..
EXEC uspFoo @id, @assayType, @status output

PRINT @status

GO

The return value, or in this case status, will either be 0 (false) or 1 (true).

How can I return a value (e.g. bit) directly without having to store it in a temporary output parameter?

You might consider scalar user defined function which you might call from a query, unlike stored procedure. — Nikola Markovinović
– Nikola Markovinović, Commented Jul 31, 2012 at 22:29

csm8118 · Accepted Answer · 2012-07-31 15:29:58Z

6

In cases like this you can use the return value from the stored procedure. The following should work.

CREATE PROC uspFoo
(
@id int,
@type nvarchar(255)
)
AS
IF ....
  RETURN 1
ELSE
  RETURN 0
GO

To call:

DECLARE @id int, @type nvarchar(255), @status bit
SET @id=..
SET @type=..
EXEC @status=uspFoo @id, @assayType

PRINT @status

answered Jul 31, 2012 at 15:29

csm8118

1,2139 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1