I have created a User-defined Type like this
CREATE TYPE [dbo].[MyType] AS TABLE(
[Template] [varchar](50) NOT NULL,
[Cust_Name] [varchar](50) NOT NULL,
[Invoice_No] [int] NOT NULL,
[InvoiceDate] [date] NOT NULL,
Sr_No int,
CurrencyCode varchar(50),
Amount money,
[Subject] varchar(MAX),
Reference varchar(MAX),
CustAddress1 varchar(MAX),
CustAddress2 varchar(MAX),
CustAddress3 varchar(MAX),
CustAddress4 varchar(MAX),
CustAddress5 varchar(MAX),
CustAddress6 varchar(MAX),
CustomerTelephone varchar(MAX),
Emailto varchar(50),
EmailCC varchar(50),
BankName varchar(MAX),
AccountTitle varchar(MAX),
AccountNo varchar(50),
CurrencyCode1 varchar(50),
BankAddress1 varchar(MAX),
BankAddress2 varchar(MAX),
BankAddress3 varchar(MAX),
BankAddress4 varchar(MAX),
SwiftCode varchar(50),
ContactName1 varchar(50),
ContactEmail1 varchar(MAX),
ContactTel1 varchar(50),
ContactName2 varchar(50),
ContactEmail2 varchar(MAX),
ContactTel2 varchar(50),
ContactName3 varchar(50),
ContactEmail3 varchar(MAX),
ContactTel3 varchar(50)
)
GO
And here is how I am using this type to declare a table
ALTER PROCEDURE [dbo].[spInsertDummy]
@tblInvoice MyType READONLY
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS (Select InvoiceNo from tblInvoice)
Begin
INSERT into tblInvoice(Template,CustomerName,InvoiceNo,InvoiceDate,Sr_No,CurrencyCode,[Subject],Reference,CustAddress1,CustAddress2,CustAddress3,CustAddress4,CustAddress5,CustAddress6,CustomerTelno,EmailTo,EmailCC,BankName,AccountTitle,AccountNo,BankAddress1,BankAddress2,BankAddress3,BankAddress4,SwiftCode,ContactName1,ContactEmail1,ContactTel1,ContactName2,ContactEmail2,ContactTel2,ContactName3,ContactEmail3,ContactTel3)
SELECT Template,Cust_Name,Invoice_No,InvoiceDate,Sr_No,CurrencyCode,[Subject],Reference,CustAddress1,CustAddress2,CustAddress3,CustAddress4,CustAddress5,CustAddress6,CustomerTelephone,EmailTo,EmailCC,BankName,AccountTitle,AccountNo,BankAddress1,BankAddress2,BankAddress3,BankAddress4,SwiftCode,ContactName1,ContactEmail1,ContactTel1,ContactName2,ContactEmail2,ContactTel2,ContactName3,ContactEmail3,ContactTel3 FROM @tblInvoice
END
Else
Raiserror('You have already uploaded this file',16,1)
END
On declare table of type MyType
I am getting this error
The parameter @tblInvoice cannot be declared READONLY since it is not a table valued Parameter
And when I removed READONLY
from the declaration I got the following error
Parameter @tblInvoice has an invalid data type
I am unable to identify whats wrong.