November 16, 2004 at 5:26 pm
I have a stored procedure that declares a float(53) variable and sets a float value = to it. The problem is, even though the variable is declared with double precision (float(53)), which is supposed to be 15 significant digits, when the variable is used in dynamic SQL (which I need to use here) the data gets mangled into single precision (float(24)), with only 7 significant digits.
The simplified version of this is such:
DECLARE @a as float(53)
DECLARE @DynSQL varchar(1000)
SELECT @a = 367970.45
SELECT @DynSQL = 'SELECT ' + cast(@a as varchar(50))
SELECT @a
EXEC (@DynSQL)
Notice that the SELECT returns:
367970.45000000001
Notice that the EXEC(@DynSQL) returns:
367970
WTF? Any ideas?
A.J.
DBA with an attitude
November 16, 2004 at 5:52 pm
I think it is the cast to varchar where you are losing data. Try using the STR() function:
DECLARE @a as float(53)
DECLARE @DynSQL1 varchar(1000), @DynSQL2 varchar(1000)
SELECT @a = 367970.45
SELECT @DynSQL1 = 'SELECT ' + cast(@a as varchar(50))
SELECT @DynSQL2 = 'SELECT ' + str(@a,50,11)
SELECT @a
EXEC (@DynSQL1)
EXEC (@DynSQL2)
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 16, 2004 at 6:21 pm
Since your example works, then it must be that when you do a Print statement it is implicitly casting it to a varchar. Does this sound right?
A.J.
DBA with an attitude
November 16, 2004 at 8:08 pm
Yes - Print does an implicit conversion to char or varchar if it can.
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 17, 2004 at 8:46 am
Thanks Phil! I appreciate your help.
A.J.
DBA with an attitude
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply