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

Solution 1

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

Solution 2

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)

Solution 3

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.

Comments

[Adriaan Stander]

Where do you declare @variableName?

[pghtech]

@Nishant SQL server 2008

[pghtech]

@astander the (@ServiceTypes dbo.ServiceType READONLY)

[pghtech]

Ok, once i have I get the error that I can't set it as READONLY because @ServiceTypes is not a table-valued parameter I'll update my question.

[p.campbell]

@pghtech: is that a runtime error, or is that SSMS with a red squiggly and the hover tooltip with that error? I've repro'd in my test environment exactly as you'd have it, and I indeed see the same error, only in the tooltip. When executing the ALTER query, it executes fine with no problems.

[rfmodulator]

+1 However I still see the red squiggly after adding a second parameter of type int. Infact, red squigglies are everywhere in my procedure's body. The query executes just fine though, so good enough for now.

[julianox]

this should be marked as the right anwser; it worked like a charm for me. Tks a lot

[pollirrata]

That problem was driving me crazy, and this answer was the one that helped me out :) Thanks!

[StackAddict]

@pghtech... Mark this as the answer

[bkwdesign]

oh, man, how long I've wished I had known this shortcut. Wish I could upvote this more

[Joe Cullinan]

This bug is still in effect in SQL Server 2017, shows red squggle, mouse-over shows an error like OP was describing, but the command runs fine. Thanks!

[Ben]

If you're working in SSMS, you can do this by toggling IntelliSense off and on at Query -> IntelliSense Enabled

[user3215480]

Worked! Thank you.

[Andrei Khotko]

I spent several hours to find out that this problem is caused by intellisence cache issue, wow. This should be marked as the answer. Thanks a lot!

[arao6]

To future readers: to fix the red squiggly in SSMS, I had to exit and re-open SSMS after creating the type. Not sure why, but the query editor wouldn't recognize the type no matter what otherwise.