Clever Geek Handbook

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 create a stored procedure that takes 4 parameters based on table fields, how to do it. I have this solution:

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

This works, except that I think @TestDecimal loses the decimal part, converting it to an integer digit. Do I need to put @TestDecimal in decimal (Precision, Scale) instead of decimal? and if so, are there any other numeric data types I need to specify for this parameter encoding type?

+2


source to share


2 answers


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

Same goes for float / real, (n) varchar, (n) char, (var) binary, datetime2 (missing any?)



Different precision, scale, or length is a different data type and the conversion will happen.

An example of a question as to why differenmt varchar lengths creates different data types

+5


source


The parameter type must match the type of the database column. The type of a database is determined not only by its base type, but also by its actual length and precision when applied. TestDecimal

id actualy DECIMAL(18,2)

in your example.



0


source






More articles:


All Articles