Can a stored procedure with input variables display an output table when parameter is selected?

Question

Edit: I think I need to add OUTPUT in my code, but where and how do I change the automated Exec script to return the table?

I have created a stored procedure with multiple input parameters, that depending on the input will produce a table output displayed in the results window.

The stored procedure creates without issue. But when I execute there is an error, as it's not outputting a int. When I select the stored procedure and execute it, I get this error:

Msg 103010, Level 16, State 1, Line 1
Parse error at line: 3, column: 1: Incorrect syntax near 'EXEC'.

The automated script is:

DECLARE @return_value int

EXEC    @return_value = [ana].[spPlan_Acitivity_Data_Select_Parameters]
        @PlanTypeID = 2,
        @PlanID = NULL,
        @FYShort = N'22/23'

SELECT  'Return Value' = @return_value
GO

I can also run the script as is, outside of a stored procedure, with defined parameters and it returns the expected table.

Additionally, I am using SSMS as the Azure GUI does not work for me.

My stored procedure is scripted like below:

ALTER PROCEDURE myschema.spPlan_Acitivity_Data_Select_Parameters
    (@PlanTypeID INT , 
     @PlanID INT ,
     @FYShort nvarchar(5))
AS
BEGIN

IF @PlanTypeID = 1
BEGIN
        /*From Base data monthly split for selected year*/
        With CTE_BASE 
        AS (
            SELECT *
            FROM [myschema].[BaseData]  BD
            Join ref.calendar CAL       ON BD.[date] = CAL.[date]

            Where CAL.FinanicalYearShort = @FYShort
        ) ,
        ----------------------------------------------------------------------------
        /*CONCAT column for join in next CTE*/
        CTE_Activty_Sum
        AS (
            Select  *
                    , Count(Case WHEN FinanicalMonth = 1 THEN EventKey END) AS M1_ActualActivity
                    , Count(CASE WHEN FinanicalMonth = 2 THEN EventKey END) AS M2_ActualActivity
                    , Count(CASE WHEN FinanicalMonth = 3 THEN EventKey END) AS M3_ActualActivity
                    , Count(CASE WHEN FinanicalMonth = 4 THEN EventKey END) AS M4_ActualActivity
                    , Count(CASE WHEN FinanicalMonth = 5 THEN EventKey END) AS M5_ActualActivity
                    , Count(CASE WHEN FinanicalMonth = 6 THEN EventKey END) AS M6_ActualActivity
                    , Count(CASE WHEN FinanicalMonth = 7 THEN EventKey END) AS M7_ActualActivity
                    , Count(CASE WHEN FinanicalMonth = 8 THEN EventKey END) AS M8_ActualActivity
                    , Count(CASE WHEN FinanicalMonth = 9 THEN EventKey END) AS M9_ActualActivity
                    , Count(CASE WHEN FinanicalMonth = 10 THEN EventKey END) AS M10_ActualActivity
                    , Count(CASE WHEN FinanicalMonth = 11 THEN EventKey END) AS M11_ActualActivity
                    , Count(CASE WHEN FinanicalMonth = 12 THEN EventKey END) AS M12_ActualActivity
            ---------------------------------------------------------------------------------------------------------------------------------
                    , Count(CASE WHEN FinanicalMonth = 1 THEN EventKey END) AS YTDM1_ActualActivity
                    , Count(CASE WHEN FinanicalMonth between 1 and 2 THEN EventKey END) AS YTDM2_ActualActivity
                    , Count(CASE WHEN FinanicalMonth between 1 and 3 THEN EventKey END) AS YTDM3_ActualActivity
                    , Count(CASE WHEN FinanicalMonth between 1 and 4 THEN EventKey END) AS YTDM4_ActualActivity
                    , Count(CASE WHEN FinanicalMonth between 1 and 5 THEN EventKey END) AS YTDM5_ActualActivity
                    , Count(CASE WHEN FinanicalMonth between 1 and 6 THEN EventKey END) AS YTDM6_ActualActivity
                    , Count(CASE WHEN FinanicalMonth between 1 and 7 THEN EventKey END) AS YTDM7_ActualActivity
                    , Count(CASE WHEN FinanicalMonth between 1 and 8 THEN EventKey END) AS YTDM8_ActualActivity
                    , Count(CASE WHEN FinanicalMonth between 1 and 9 THEN EventKey END) AS YTDM9_ActualActivity
                    , Count(CASE WHEN FinanicalMonth between 1 and 10 THEN EventKey END) AS YTDM10_ActualActivitY
                    , Count(CASE WHEN FinanicalMonth between 1 and 11 THEN EventKey END) AS YTDM11_ActualActivity
                    , Count(CASE WHEN FinanicalMonth between 1 and 12 THEN EventKey END) AS YTDM12_ActualActivity
            ---------------------------------------------------------------------------------------------------------------------------------
            From CTE_Base CB
            Group By [All Columns]
        ) ,
        -----------------------------------------------------------------------------------
        /*Joining columns*/
        CTE_3 AS
        (
            Select *
                , Getdate() AS Runtime
            From  CTE_Activty_Sum CAS
            LEFT Join [myschema].[Secondary_table] AS ST        ON  CAS.CONCAT_Check = ST.CONCAT_Check
            Where PlanID = @PlanID
        )

        /*This is my expected output*/
        Select *
        From CTE_3

END

IF @PlanTypeID = 2
BEGIN 
 
Print 'Business' + ' ' + @FYshort
END 

IF @PlanTypeID = 3
BEGIN 
 
Print 'Internal'  + ' ' + @FYshort
END 

IF @PlanTypeID = 4
BEGIN 
 
Print 'Operational'  + ' ' + @FYshort
END 

END
GO

Answer 1

Without changing the Stored Procedure script, use the following script

exec [ana].[spPlan_Acitivity_Data_Select_Parameters] 1, 1, '22/23'

…in a new query, and the table will output.

You can NOT use the right click and Execute Stored Procedure.

Answer 2

AS @larnu said EXEC @return_status = module_name syntax is not supported in Azure Synapse also, in Microsoft Document this support is omitted for Synapse analytics.

Can a Stored Procedure with input variables display an output table when parameter is selected

It will display an output table. but you cannot store it in variable. I tried to reproduce it and getting result as follow:

I think I need to add OUTPUT in my code, but where and how do I change the Automated Exec script to RETURN the table?

You can try input and output parameter like this,

Example:

create  procedure demo19 (@PlanID INT ,@MANDT nvarchar(225) output,@VBELN nvarchar(225) output,@posnr nvarchar(225) output) AS
BEGIN
select @MANDT = MANDT,@VBELN = VBELN,@posnr = posnr from lips where MANDT = @PlanID
End

declare @MANDT varchar(225), @VBELN varchar(225), @posnr varchar(225)
exec demo19 510, @MANDT output, @VBELN output, @posnr output
select @MANDT, @VBELN, @posnr

Execution: