How do you select a stored procedure's return value?

Question

It seems like this would would be trivial, but my SQL is not up to par and I can't figure it out. I just want to select a returned scalar from a stored procedure. Basically like this:

select dbo.sProcedure @param

But that throws an error. It's not really an option to return a result set from within the procedure. Is it possible to do this, or do I need to start rewriting some code?

Answer 1

You should pass the sp an output parameter where you can store the required returned value, or use an scalar function

Answer 2

Something like this:

DECLARE @SomeReturnValue INT
EXEC @SomeReturnValue = SomeProc
SELECT @SomeReturnValue

Answer 3

@RichardOD has the right syntax, however as implied, you can't use it in a select statement. If you need to use it in a select statement, then use a function instead of a stored procedure.