SQL problem with error "Invalid data type"

Question

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].[ImportServiceTypeRates]  (@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

Answer 1

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)

Answer 2

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