creating SQL table from User Defined Type

137 views Asked by At

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.

0

There are 0 answers