Declare and assign result from stored procedure in one line

Question

Is it possible to declare and assign the result from an sp in one statement? If we can do,

DECLARE @lastid INTEGER = (SELECT MAX([Id]) FROM [ADVWKS].[dbo].[Account]);

why cant we do the below one?

DECLARE @lastaccid INTEGER = (EXEC sp_GetGlobalVarVal 'LAST_ACCID');

So far you need one statement to declare the variable and one to execute the procedure and assign the variable as return, output, cursor etc. Can we do it in a single statement??

Answer 1

You cannot do the below because it is often the case that the return type for the stored procedure is a table. It would either execute, and run DML statements which wont return anything, or it would return data set. Now what you can do is to build a scalar value function instead of stored procedure and select from that. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-2017

Another possibility is using: https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-2017#returning-data-using-a-return-code, but conceptually per article, this is only used for Return Codes, not really for the variable assignment or populating variables for different types, and do not forget, there is only values between 0 - 4.

Answer 2

You can use OUTPUT Parameters:

This is an example SP where the second variable (@Param_2) is defined as output Parameter

CREATE   PROCEDURE [dbo].[MySP]
(
    @Param_1 int,
    @Param_2 int output
)
AS
BEGIN
    SET NOCOUNT ON;

    SET @Param_2 = 10 * @Param_1;
END

Now you can call the SP like this:

DECLARE @Value_1 INT = 42;
DECLARE @RetVal INT = 0;

SELECT @RetVal;
EXEC [dbo].[MySP] @Value_1, @Param_2 = @RetVal OUTPUT;
SELECT @RetVal;

The frist time you select @RetVal the value will still be 0. After the execution of the SP, @RetVal will be 420 (10 * 42 calcualted inside the SP):

-----------
0

(1 row affected)


-----------
420

(1 row affected)

TLDR: No :)