开发者

SQL problem with error "Invalid data type"

Using 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"

ALTER PROCEDURE [dbo].[ImportServi开发者_如何学JAVAceTypeRates]

(@ServiceTypes dbo.ServiceType)  --I have tried it without the "dbo." as well

Update

So I added "READONLY" to my variable declaration

@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?

I thought the "CREATE TYPE ServiceType as TABLE" was what declared it such???

I also have showing in Types\User-Defined Table Types\dbo.ServiceType


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)


You must declare your table-valued parameter as 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.

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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜