none
Parameter Mapping Output on Stored Procedure RRS feed

  • Question

  • I have a stored procedure where an Input (@LastMod) and an Output (@TotRecs) is used in a stored procedure.  Below is the syntax for the SP

     @LastMod varchar(50),
     @TotRecs int OUTPUT,
    
     Set @TotRecs =(Select Count(*) From  [Server].[Live].[Issue_Open]   WHERE Date_Modified >=  @LastMod )

    Theres more to it than the above but that's the part that it's failing on. I have the variable that I'm capturing the @TotRecs total with set as below.

    Direction: Output

    Data Type Long

    Also, the variable that is used to store the value is int32

    If the count is under 100 it works fine, it's when it's more than 100. Any assistance would be appreciated.


    • Edited by DanHaf Tuesday, June 6, 2017 1:34 PM
    Tuesday, June 6, 2017 1:06 PM

Answers

  • Hi DanHaf,

    Sorry, I can not reproduce the issue. Please refer to following screenshots:

    sample data.

    create PROCEDURE OutputParameter
    @amount int,
    @Total int Output
    
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	set @Total=(select count(*) from [AdventureWorks2012_Data].[dbo].[FactFinance] where FinanceKey>@amount)
    	return @Total
    END
    GO

    Variables in a SSIS package.

    EXEC ? = OutputParameter ?, ? OUTPUT

    In my scenario, it can return the correct result for @output: 39409 rows.

    Please make sure you could return the correct result (>100) in SSMS and please make sure the configuration is correct.

    You could refer to: Stored Procedure Return Values and Output Parameters in SSIS

    Best Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by DanHaf Monday, June 12, 2017 1:51 PM
    Wednesday, June 7, 2017 8:21 AM
    Moderator

All replies

  • What is the error?

    Arthur

    MyBlog


    Twitter

    Tuesday, June 6, 2017 1:38 PM
    Moderator
  • Hi DanHaf,

    Sorry, I can not reproduce the issue. Please refer to following screenshots:

    sample data.

    create PROCEDURE OutputParameter
    @amount int,
    @Total int Output
    
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	set @Total=(select count(*) from [AdventureWorks2012_Data].[dbo].[FactFinance] where FinanceKey>@amount)
    	return @Total
    END
    GO

    Variables in a SSIS package.

    EXEC ? = OutputParameter ?, ? OUTPUT

    In my scenario, it can return the correct result for @output: 39409 rows.

    Please make sure you could return the correct result (>100) in SSMS and please make sure the configuration is correct.

    You could refer to: Stored Procedure Return Values and Output Parameters in SSIS

    Best Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by DanHaf Monday, June 12, 2017 1:51 PM
    Wednesday, June 7, 2017 8:21 AM
    Moderator
  • Thanks all. I got it working. I'm not sure exactly what the issue was. I tried a few different data types and then when I went back to long, it worked fine. Just a bug, I guess. Thanks again.
    Wednesday, June 7, 2017 11:22 AM
  • Hi DanHaf,

    Would you please mark an appropriate a reply as answer to close this topic if possible?

    Thanks,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 8, 2017 1:36 AM
    Moderator