I have stored procedures:

1. [SP_Fetch_Budget_Data_ADF]
2. SP_Fetch_Data_ADF

Both these stored procedures have the same parameters:

CREATE PROCEDURE [DW].[SP_Fetch_Budget_Data_ADF]
    @Date varchar(10), 
    @Entity varchar (12), 
    @Scenario varchar(10) 

Parameters are passed from other tool. My goal is to write a 3rd stored procedure where I pass the parameter @Scenario varchar(10) (same parameter as for both stored procedures as shown above, so this parameter is supposed to be passed to all 3 stored procedures).

If @Scenario = "actual" then execute SP_Fetch_Data_ADF else execute SP_Fetch_Budget_Data_ADF.

I found this answer but I did not understand how to pass the parameter.

UPDATE

Below is my code, but I get this error:

Could not find stored procedure 'SP_Fetch_Budget_Data_ADF'

Code:

CREATE PROCEDURE [DW].[SP_EXECUTE_ADF] 
    @Scenario varchar(10)  
AS
BEGIN
    SET NOCOUNT ON;
   
    DECLARE @queryToRun NVARCHAR(128) = 'EXECUTE ';

    SELECT @queryToRun = @queryToRun   CASE
                                         WHEN @Scenario  = 'actual'
                                            THEN 'DW.SP_Fetch_Data_ADF'
                                         ELSE 'DW.SP_Fetch_Budget_Data_ADF'
                                      END

    EXECUTE sp_executesql @queryToRun;
END

CodePudding user response：

Just use an IF statement to choose which procedure to call (and get rid of the sp_ prefix; stored procedures shouldn't have any prefix, let alone the special sp_ prefix). EG

CREATE OR ALTER PROCEDURE DW.Fetch_Budget_Data_ADF
    @Date varchar(10), 
    @Entity varchar (12)
as
select 1 a
go
CREATE OR ALTER PROCEDURE DW.Fetch_Data_ADF
    @Date varchar(10), 
    @Entity varchar (12)
as
select 2 a

go

CREATE PROCEDURE DW.EXECUTE_ADF
    @Date varchar(10), 
    @Entity varchar (12), 
    @Scenario varchar(10) 
as
begin
  if @Scenario = 'actual'
  begin
    exec Fetch_Data_ADF @Date, @Entity
  end
  else
  begin
    exec Fetch_Budget_Data_ADF @Date, @Entity
  end

end