May 23, 2012 at 10:02 am
I am executing a stored procedure from an Execute SQL Task that doesn't appear to be doing anything. The procedure performs a few numeric calculations and executes an update statement if a condition is true. Basically when I test it, it should be executing the update statement and it never does seem to meet the condition when I run it through SSIS. I am using verison 2005.
I am using the OLE DB connection. I am passing in three parameters from variables (listed with their data types):
LUKey - String
OPrc - Double
PrcDiff - Double
In the input parameter screen I have the following:
LUKey - Input - VARCHAR - 0
OPrc - Input - DOUBLE - 1
PrcDiff - Output - DOUBLE - 2
Here is the structure and syntax of the procedure. Maybe someone can notice something that I do not see.
CREATE PROCEDURE [dbo].[usp_Procedure1]
(@LUKey varchar(50),
@OPrc money,
@PrcDiff money OUTPUT)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SumPrc money
SET @SumPrc = (SELECT SUM(Price)
FROM Table1
WHERE Category2 = @LUKey)
SET @PrcDiff = @OPrc - @SumPrc
IF NOT @PrcDiff = 0
BEGIN
UPDATE Table2
SET Price = (Price + @PrcDiff), Category4 = @PrcDiff
WHERE Category2 = @LUKey
END
END
When I put breakpoints on the PreExecute and PostExecute I see the variable PrcDiff start at a value of {0} and then on PostExecute it shows a value of {2.35944361387577E-315} which kind of makes me think that the could be a datatype issue somewhere here. No errors occur, but the update statement is not being executed when it should based on the condition.
Any ideas?
May 23, 2012 at 10:20 am
May be nothing, but just for giggles (and no, I don't know who giggles is), try changing the data types from money to decimal(20,4) (just to give values).
May 23, 2012 at 10:28 am
Thanks for the reply, but the result is the same. I think it may have to do with the data type of the variable, but I have used the double data type many times before without any issue.
May 23, 2012 at 10:33 am
This value, 2.35944361387577E-315, maybe zero in the stored procedure and that is why the update doesn't occur.
What you may be seeing is a conversion issue between decimal(money) and float data types.
May 23, 2012 at 10:48 am
That is what I was thinking as well. So just to try something, I used the string data type and then put a few conversion in the stored procedure and it came up with an empty string. I have another stored proc that does something very similar and not having any issues like this.
May 23, 2012 at 9:08 pm
Tuned out to be because the query that is assigning a value to @SumPrice didn't return any records. So when you do any math operation on a NULL value it is always give you a NULL result. I guess when you pass a NULL value back to SSIS to store in a variable with a datatype of Double it assigns a value of {2.35944361387577E-315}.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply