用另一个SP的输出参数调用SP

Question

我只是想从另一个SP调用SP，但在调用SP中找到输出参数时卡住了。我使用EXECUTE sp_executesql调用存储过程。

这是我的SP的一部分

declare @FinalQuery NVARCHAR(max) 
            DECLARE @ParmDefinition NVARCHAR(500)      
            DECLARE @parmIN VARCHAR(10)
            DECLARE @pin_codeRET NVARCHAR(20)
            DECLARE @ffi_idRET int
            set @FinalQuery = 'EXEC  [AddFirefighter] ' +  @SingleQuery                          
                SET @parmIN=' returned'             
                SET @ParmDefinition=N'@last_name nvarchar(50),
                                @first_name nvarchar(50),
                                @rnk_code nchar(6),
                                @ems_code nchar(6),
                                @fst_code nchar(3),
                                @sta_id int = NULL,
                                @travel_distance int,
                                @active bit,
                                @dep_id int = Null,
                                @spcl_code nchar(6),
                                @email nvarchar(255) = null,
                                @mobile_number nvarchar(500),
                                @password nvarchar(50) = NULL,
                                @uty_code nchar(3),
                                @default_tab_id  INT,
                                @pin_code NVARCHAR(20) OUTPUT,
                                @ffi_id int OUTPUT,
                                @status BIT,
                                @memberrole_type NVARCHAR(50),
                                @ischangepasatlogin BIT,
                                @Notification BIT = 1,
                                @FwdResponderInfo Bit=null,
                                @PagerSerialNo varchar(20)=null,
                                @smsgt_id int = NULL'             
             EXECUTE  sp_executesql  
             @FinalQuery,
             @ParmDefinition,  
                @last_name  = @parmIN,
                @first_name = @parmIN,
                @rnk_code = @parmIN,
                @ems_code = @parmIN,
                @fst_code = @parmIN,
                @sta_id = @parmIN,
                @travel_distance = @parmIN,
                @active = @parmIN,
                @dep_id = @parmIN,
                @spcl_code = @parmIN,
                @email = @parmIN ,
                @mobile_number = @parmIN,
                @password = @parmIN ,
                @uty_code = @parmIN,
                @default_tab_id  = @parmIN,
                @pin_code = @pin_codeRET OUTPUT,
                @ffi_id = @ffi_idRET  OUTPUT,
                @status = @parmIN,
                @memberrole_type = @parmIN,
                @ischangepasatlogin = @parmIN,
                @Notification = @parmIN ,
                @FwdResponderInfo = @parmIN,
                @PagerSerialNo = @parmIN,
                @smsgt_id = @parmIN

@SingleQuery的值为'Vijay','Rana','AC','AC','VOL',1,0,1,1,'NA','[e-mail]','8699500665','40733','USR',0,@pin_code OUTPUT, @ffi_id OUTPUT ,'','FirefighterLevel',0,1,1,'',0

ANd Error Message is Error Message = Must declare the scalar variable "@ffi_id"

Answer 1

我解决了我的问题，我需要声明@pin_code和@ffi_id

    set @FinalQuery =  ' declare @pin_code NVARCHAR(20),  @ffi_id int exec   [AddFirefighter] ' +  @SingleQuery     
 EXECUTE sp_executesql   @FinalQuery