T-SQL Parameters

Question

I have the following table in SQL Server 2000:

TABLE_NAME         |     COLUMN_NAME      |     TYPE_NAME      |    PRECISION    |    LENGTH  |    SCALE        |
test                     TestID                 int                 10                   4            0
test                     TestDecimal            decimal             18                   20           2
test                     TestFloat              float               15                   8            NULL
test                     TestMoney              money                19                   21            4

My question is, if I wanted to created a stored procedure that takes 4 parameters based on my table fields, how do I do this. I have this solution:

CREATE PROCEDURE TestProc ( @TestID int, @TestDecimal decimal, @TestFloat float, @TestMoney money ) 
AS
.....
.....
.....
GO

This works, except I think @TestDecimal loses its decimal portion, thus converting it into a whole number. Do I need to put @TestDecimal decimal(Precision,Scale) instead of just decimal? and if so, is there any other numeric datatypes that I need to specify this type of parameter encoding?

You win the award for nicest looking table definition I've seen so far (and I've seen a few here). — Joel Coehoorn, Oct 26 '09 at 20:22

score 5 · Accepted Answer · edited May 23 '17 at 10:33

5

Yes, you need to specifc (18,2) for decimal/numeric

The same applies to float/real, (n)varchar, (n)char, (var)binary, datetime2 (missed any?)

A different precision, scale or length is in effect a different datatype and a conversion will occur.

Example question of why differenmt varchar lengths make different datatypes

edited May 23 '17 at 10:33

Community

1
1

answered Oct 26 '09 at 20:22

gbn

422,506
82
585
676

You'd presumably only have to specify scale and precision if they're other than the default. (I've never specified it for any of my floats and reals.) – Philip Kelley Oct 26 '09 at 20:40
@Philip: I'd always specify except for float/real (which I don't really use). For example I wouldn't rely on varchar length = 30 in a CAST: I'd define explicitly, especially decimal because of how * and / are handled (http://stackoverflow.com/questions/423925/) – gbn Oct 26 '09 at 20:44
Also see https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx – Aaron Bertrand Oct 26 '09 at 21:43

score 0 · Answer 2 · answered Oct 26 '09 at 20:23

0

Your parameter type must match the database column type. A database type is defined not only by its base type, but also by its actual length and precision, when it applies. TestDecimal id actualy DECIMAL(18,2) in your example.

answered Oct 26 '09 at 20:23

Remus Rusanu

288,378
40
442
569

T-SQL Parameters

2 Answers2