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.

Answer 1

The same code works for me too. But there is no statements between BEGIN and END.Try by adding some statements like this

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create  PROCEDURE [dbo].[GetTopTopic](
   @dt  [dbo].[ApproveAddsIds] READONLY  
      )   
AS
BEGIN
--Write some statements here
print 'hi'
END

Answer 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 :)

Answer 3

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.

Answer 4

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

Answer 5

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.