SQL Server Performance Forum – Threads Archive

DataType Prob

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 @[email protected]@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

---

Hi ya, you really need to make sure that the datatype for the sp parameters matches the table… in your case you have some sp datatypes defined as decimal(18,0) going into a decimal(18,2) column. Passing 0.9 to that will cause it to be rounded up to 1 Cheers
Twan

---

damn it that was such a simple thing thanks for spottin it though i appreciate it

---

]]>