views:

111

answers:

2
+2  Q: 

T-SQL Parameters

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?

+5  A: 

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

gbn
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
@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
Also see http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx
Aaron Bertrand
A: 

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.

Remus Rusanu