Invalid data type while using user defined table type

user_2126009 0
Viewed 24179

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.

sql-types stored-procedures sql-server sql database
5 Answers

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

user_32233540

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.

user_26033450
Related