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 t开发者_运维技巧akes 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?
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
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.
精彩评论