在 nvarchar 声明中省略 size 有什么影响 [英] What is the effect of omitting size in nvarchar declaration

问题描述

我通常在我的 SP 中声明参数时定义大小，例如:

I usually define size when declaring parameters in my SP, like :

@myParam nvarchar(size)

或者当我投射或转换时:

or when I casting or converting:

CAST(@myParam AS nvarchar(size))

最近我从 CAST 函数中删除了大小，例如:

Recently I've removed size from my CAST functions like:

CAST(@myParam AS nvarchar)

而且我有点担心这是否会在最意想不到的时候咬我:-(，因为我注意到在使用递归 CTE 并在不指定大小的情况下转换 nvarchar 时 nvarchar 变量被截断.

and I'm bit worried if that is going to come and bite me when least expected :-(, since I noticed truncation on nvarchar variables when using recursive CTE and casting nvarchar without specifying size.

有什么意见吗?

推荐答案

如果省略大小，则默认为 30.看这个:

If you omit the size, it defaults to 30. See this:

http://msdn.microsoft.com/en-us/library/ms186939.aspx

要查看实际效果，请尝试执行以下语句:

To see this in action, try executing the following statements:

--outputs: 12345678901234567890.098765432 
select cast (12345678901234567890.098765432 as nvarchar)

--throws "Arithmetic overflow error converting expression to data type nvarchar."
select cast (12345678901234567890.0987654321 as nvarchar) 

--outputs: 12345678901234567890.0987654321 
select cast (12345678901234567890.0987654321 as nvarchar(31))

根据@krul 的评论；30 是 CAST 的默认长度；然而，数据定义或变量声明的默认长度为 1.

Per @krul's comment; 30 is the default length for CAST; however the default length for a data definition or variable declaration is 1.

注意:还有一个 STR 将数字字段转换为字符串的函数，默认长度为 10.

NB: There's also an STR function which converts numeric fields to strings, for which the default length is 10.

--outputs: 1234567890
select str(1234567890)

--outputs: **********
select str(12345678901)

--outputs: 12345678901
select str(12345678901,11)

这篇关于在 nvarchar 声明中省略 size 有什么影响的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！