Invalid data type while using user defined table type

Question

I'm new to table-valued parameter in SQL Server 2008. I tried to make user-defined table with query

USE [DB_user]
GO
CREATE TYPE [dbo].[ApproveAddsIds] AS TABLE(
    [Ids] [bigint] NULL
)
GO 

When I tried to use the table type in stored procedure

USE [DB_user]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create  PROCEDURE [dbo].[GetTopTopic]
    @dt  [dbo].[ApproveAddsIds] READONLY      
AS
BEGIN

END

I got two errors_

@dt has an invalid data type
Parameter @dt cannot be declared read only since it is not table-valued parameter.

So I tried to figure out reason behind this as first query is executed successfully I thought its because of permissions and so tried

GRANT EXEC ON TYPE::[schema].[typename] TO [User]
GO

But error continues don't know whats wrong with this.

Something weird I noticed right now when I put , after @dt [dbo].[ApproveAddsIds] READONLY above error removed and now error is on AS Saying expecting variables. When I write code for variables old error continued. I think it might help.

Solution 1

I had this exact problem happening to me. I simply made sure I had saved everything I needed in SQL Server and restarted the SQL Server Management Studio.

Once restarted, the errors no longer appeared. I am using SQL Server 2012 but I don't see why this shouldn't also work with 2008.

Solution 2

I know I am late to the party but actually what you see is the problem with SSMS Intellisense and you can easily resolve it without restarting whole SSMS. Just press CTRL-SHIFT-R. Wait a few moments and problem is resolved :)

Solution 3

I have had this same issue after creating a new new user-defined table type (UDTT) in one query window and then trying to create a new stored procedure in another. As per another answer to this question, I solved it by restarting SQL Server Management Studio. However it annoyed me that I had to restart the application as I had plenty of other work open/in-progress.

In my case I had noticed that IntelliSense in SSMS had underlined the name of my UDTT with a red squiggly line. I found the following advice...

By far the most common scenario is that your local, in-memory cache is stale. This can be more prevalent if you switch databases often using the USE command, or if the database is changing in other query windows or by other users. You can refresh the cache by pressing Ctrl+Shift+R or by selecting the menu option Edit > IntelliSense > Refresh Local Cache. As above, you may need to wait for the cache to fully load, depending on the size of your metadata and other resources involved...

... on the following web page ...

https://www.mssqltips.com/sqlservertip/2591/troubleshooting-intellisense-in-sql-server-management-studio-2012/

The advice is echoed on MSDN, with another bit of advice here:

https://msdn.microsoft.com/en-us/library/ms173434.aspx

IntelliSense functionality does not cover database objects created by another connection after your editor window connected to the database.

I cannot be sure whether refreshing the IntelliSense cache would have any effect on errors thrown when actually trying to create the Stored Procedure, but since the restart of SMSS solved the problem with no other changes to the script, a caching problem does sound about right.

user-defined-type sqlservermanagementstudio intellisense

Solution 4

I was facing the same issue. It was indeed related to IntelliSense. Following are the steps that I performed to fix it. I am using SQL Management Studio 2017.

1) In the Code Editor window for Stored Procedure, right click.

2) From the short cut menu select "IntelliSense Enabled"

After that the code editor did not show any error. Hope this helps.

View more solutions

Comments

[Radhamani Muthusamy]

I dont see any syntax error from the given code.Is there any query within begin and end statement ?.

[sk7730]

@HotCoolStud , Above code should work fine... What issue you are facing...Be descriptive

[Hot Cool Stud]

when i tried above code i face error dt has an invalid data type Parameter ,dt cannot be declared read only since it is not table-valued parameter.

[Charlie Joynt]

Confirmed that a restart of SMSS may not be needed by testing: (1) create a new UDTT in one query window and then (2) try to use it in a DECLARE statement in another. The UDTT was underlined/showing as an unknown type until (3) pressing CTRL+SHIFT+R reloaded the Intellisense cache and all was well.

[Dan Cook]

Restarting SSMS worked for me with SQL Server 2014. Check that the correct database is selected too,.

[Arslan Elahi]

this comment by @Bogdan Sahlean actually helped. Are you sure you created the table type: CREATE TYPE ... AS TABLE ... ? Basically, the error message tells that there is no [dbo].[ApproveAddsIds] table type. – Bogdan Sahlean

[Noxxys]

Same with version 18.3.1. It looks like a long running bug...

[Werdo]

Works for me :)