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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy