Parameter Mapping Output on Stored Procedure

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.

Answer 1

What is the error?

---

Arthur

MyBlog

Twitter

Answer 2

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 [e-mail].

Answer 3

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.

Answer 4

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 [e-mail].