错误“无效数据类型”的 SQL 问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5025388/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL problem with error "Invalid data type"
提问by pghtech
Using SQL 2008
使用 SQL 2008
So I created a User Defined type:
所以我创建了一个用户定义类型：
CREATE TYPE dbo.ServiceType AS TABLE (
[TO_ZONE] varchar(30) NOT NULL,
[FROM_ZONE] varchar(30) NOT NULL,
[RATE] decimal(14,2) NOT NULL,
[SERVICE_TYPE] varchar(255) NOT NULL
);
And when I try to use it I get the error "parameter or variable @variableName has an invalid data type"
当我尝试使用它时，出现错误“参数或变量@variableName 的数据类型无效”
ALTER PROCEDURE [dbo].[ImportServiceTypeRates]
(@ServiceTypes dbo.ServiceType) --I have tried it without the "dbo." as well
Update
更新
So I added "READONLY" to my variable declaration
所以我在变量声明中添加了“READONLY”
@ServiceTypes dbo.ServiceType READONLY
And I now get the error "The parameter @Servicetype cannot be declared READONLY because it is not a table-valued parameter" ?WHAT?
我现在收到错误“参数@Servicetype 不能声明为只读，因为它不是表值参数”？什么？
I thought the "CREATE TYPE ServiceType as TABLE" was what declared it such???
我认为“CREATE TYPE ServiceType as TABLE”是这样声明的？？？
I also have showing in Types\User-Defined Table Types\dbo.ServiceType
我也在 Types\User-Defined Table Types\dbo.ServiceType 中显示
采纳答案by p.campbell
You must declare your table-valued parameter as READONLY.
您必须将表值参数声明为READONLY.
ALTER PROCEDURE [dbo].[ImportServiceTypeRates]
@ServiceTypes dbo.ServiceType READONLY
AS
--your proc
Even though SQL Server forces your table-valued parameter to be read-only, you must also explicitly declare it so in the stored procedure parameter list.
即使 SQL Server 强制您的表值参数为只读，您也必须在存储过程参数列表中明确声明它。
回答by sam
I know this is an old post but since I encountered the same issue and was able to solve it, thought of sharing it. This is an IntelliSense cache issue and could be solved by pressing ctrl+shift+R(shortcut for Edit -> IntelliSense -> Refresh Local Cache)
我知道这是一个旧帖子，但由于我遇到了同样的问题并且能够解决它，所以想分享它。这是一个 IntelliSense 缓存问题，可以通过按ctrl+ shift+来解决R（编辑 -> IntelliSense -> 刷新本地缓存的快捷方式）
回答by Bruce Patin
As long as you do not declare any parameters other than the table type in the stored procedure, you will get the red squiggly line error in Intellisense, but the stored procedure should be created or altered successfully. This appears to be an unfixed Microsoft bug.
只要在存储过程中不声明表类型以外的任何参数，就会在Intellisense中得到红色波浪线错误，但存储过程应该创建或更改成功。这似乎是一个未修复的 Microsoft 错误。
