CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008stored-procedures
pghtech
pghtech

Reputation: 3702

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].[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

Upvotes: 30

Views: 50549

Answers (3)

sam
sam

Reputation: 4397

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)

Upvotes: 148

Bruce Patin
Bruce Patin

Reputation: 355

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.

Upvotes: 9

p.campbell
p.campbell

Reputation: 100637

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

Upvotes: 14

Related Questions