February 3, 2006 at 8:47 am
hi i am havin some trouble tryin to store values less than 1 in my sql server table for some reason it seems to round up or down the number less than 1 here is the talle and the stored proc needed im just wonderin if anyone has any ideas
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PsychometricEvaluationImage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PsychometricEvaluationImage]
GO
CREATE TABLE [dbo].[PsychometricEvaluationImage] (
[PsychometricEvaluationID] [int] NOT NULL ,
[RAWACT] [tinyint] NULL ,
[RAWV1] [tinyint] NULL ,
[RAWV2] [tinyint] NULL ,
[RAWV3] [tinyint] NULL ,
[RAWV4] [tinyint] NULL ,
[RAWV5] [tinyint] NULL ,
[CACT] [tinyint] NULL ,
[CV1] [tinyint] NULL ,
[CV2] [tinyint] NULL ,
[CV3] [tinyint] NULL ,
[CV4] [tinyint] NULL ,
[CV5] [tinyint] NULL ,
[DQV1] [decimal](18, 2) NULL ,
[DQV2] [decimal](18, 2) NULL ,
[DQV3] [decimal](18, 2) NULL ,
[DQV4] [decimal](18, 2) NULL ,
[DQV5] [decimal](18, 2) NULL ,
[PS] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[CR] [decimal](18, 2) NULL ,
[DR] [decimal](18, 2) NULL ,
[CO] [decimal](18, 2) NULL ,
[SUM] [tinyint] NULL ,
[MC] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[pr_dal_PsychometricEvaluationImage_Insert]
@PsychometricEvaluationID int,
@RAWACT decimal(18, 0),
@RAWV1 decimal(18, 0),
@RAWV2 decimal(18, 0),
@RAWV3 decimal(18, 0),
@RAWV4 decimal(18, 0),
@RAWV5 decimal(18, 0),
@CACT decimal(18, 0),
@CV1 decimal(18, 0),
@CV2 decimal(18, 0),
@CV3 decimal(18, 0),
@CV4 decimal(18, 0),
@CV5 decimal(18, 0),
@DQV1 decimal(18, 0),
@DQV2 decimal(18, 0),
@DQV3 decimal(18, 0),
@DQV4 decimal(18, 0),
@DQV5 decimal(18, 0),
@PS nvarchar(10),
@CR nvarchar(10),
@DR nvarchar(10),
@CO nvarchar(10),
@SUM nvarchar(10),
@MC nvarchar(10),
@ErrorCode int OUTPUT
AS
SET NOCOUNT ON
INSERT [dbo].[PsychometricEvaluationImage]
(
[PsychometricEvaluationID],
[RAWACT],
[RAWV1],
[RAWV2],
[RAWV3],
[RAWV4],
[RAWV5],
[CACT],
[CV1],
[CV2],
[CV3],
[CV4],
[CV5],
[DQV1],
[DQV2],
[DQV3],
[DQV4],
[DQV5],
[PS],
[CR],
[DR],
[CO],
[SUM],
[MC]
)
VALUES
(
@PsychometricEvaluationID,
@RAWACT,
@RAWV1,
@RAWV2,
@RAWV3,
@RAWV4,
@RAWV5,
@CACT,
@CV1,
@CV2,
@CV3,
@CV4,
@CV5,
@DQV1,
@DQV2,
@DQV3,
@DQV4,
@DQV5,
@PS,
@CR,
@DR,
@CO,
@SUM,
@MC
)
SELECT @ErrorCode=@@ERROR
GO
declare @P1 int
set @P1=0
exec dbo.[pr_dal_PsychometricEvaluationImage_Insert] @PsychometricEvaluationID = 27,
@RAWACT = 1, @RAWV1 = 1, @RAWV2 = 1, @RAWV3 = 1, @RAWV4 = 1, @RAWV5 = 1, @CACT = 1,
@CV1 = 1, @CV2 = 1, @CV3 = 1, @CV4 = 1, @CV5 = 1,
@DQV1 = 0.980, @DQV2 = 0.870, @DQV3 = 0.540, @DQV4 = 0.310, @DQV5 = 0.840,
@ps = N'22', @CR = 2, @DR = 2, @CO = 2, @SUM = 2, @mc = 0.050, @ErrorCode = @P1 output
select @P1
February 3, 2006 at 9:03 am
Just glancing over this quickly...
You have several attributes in your table ddl as tinyint, however you do an implicit conversion of decimal datatype in your stored procedure.
I'd guess altering your ddl to incorporate the same data types you have in your stored proc would solve your problem.
HTH
Mathew J Kulangara
sqladventures.blogspot.com
February 3, 2006 at 9:34 am
@DQV4 decimal(18, 0),
@DQV5 decimal(18, 0),
Your error is in your parameter declaration.
decimal 18,0 is equal to an 18 digit number with no digits to the right of the decimal
it should be declared at minimum to what your table is which is 18,2 meanining 18 digits long with 2 digits to the right of the decimal. even here you will incurr rounding, because your scale is set to 2.
so for .840 it will save .84, but if you put .843, it will still save .84.
I would recommend your table and variables be decimal 18,3 at minimum with the example you have given
February 3, 2006 at 9:48 am
thanks very much guys i appreciate it
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply