**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 store procedure and Execute I get the error below:
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 colunms*/
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
CodePudding user response：
Without changing the Stored Procedure script, use script
exec [ana].[spPlan_Acitivity_Data_Select_Parameters] 1, 1, '22/23'
in a new query and the table will output. You can NOT used the right click and Execute Stored Procedure ...
CodePudding user response：
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:
