CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduressql-server-2012
Posting Questions
Posting Questions

Reputation: 217

Why a table type parameter is not getting declared in sp?

ALTER PROCEDURE [dbo].[InsertInvoices] 


        @ServiceIDs as dbo.ServiceIDs READONLY,

But it doesn't get declared and throws error:

Msg 2715, Level 16, State 3, Procedure InsertInvoices, Line 18 Column, parameter, or variable #8: Cannot find data type dbo.ServiceIDs. Parameter or variable '@ServiceIDs' has an invalid data type.

even though I have declared a table type.

CREATE TYPE [dbo].[ServicesIDs] AS TABLE(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ServiceID] [int] NULL,
    PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

Upvotes: 0

Views: 611

Answers (2)

mohabbati
mohabbati

Reputation: 1158

Your problem is on you spelling. Your type is [dbo].[ServicesIDs] but you use @ServiceIDs. The problem is 's' character.

ALTER PROCEDURE [dbo].[InsertInvoices] 


        @ServiceIDs as dbo.ServicesIDs READONLY,

Upvotes: 2

Rigerta
Rigerta

Reputation: 4039

It is just not working because of a typo: Your type is called [dbo].[ServicesIDs] but you are trying to use it as dbo.ServiceIDs (notice the missing 's' in the Services!

Try this:

ALTER PROCEDURE [dbo].[InsertInvoices] 
   @ServiceIDs as dbo.ServicesIDs READONLY,

Upvotes: 1

Related Questions