Invalid data type while using user defined table type

sql sql-server database stored-procedures sql-types
Hot Cool Stud picture Hot Cool Stud · Jan 2, 2014 · Viewed 21.5k times · Source

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

Anya Hope picture Anya Hope · Sep 16, 2014

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.

Related questions

How can I check if a View exists in a Database?

I have some SQL code that needs to be executed if a certain View exists in a database. How would I go about checking if the View exists? EDIT: The DBMS being used is Microsoft SQL Server

Read More
Functions vs Stored Procedures

Let's say I have to implement a piece of T-SQL code that must return a table as result. I can implement a table-valued function or else a stored procedure that returns a set of rows. What should I use? In …

Read More
Stored procedure using sp_send_dbmail to send emails to multiple recipients queried from database

Like the title says, I'm trying to make a stored procedure used to send emails to addresses stored in the database, often including multiple recipients. I've tried a few approaches to this, but I think I'm hitting a wall and …

Read More