using user defined tables in SQL Stored Procedure - invalid data type

Question

I've been struggling to get the defined data table to be accepted as a variable for one of the parameters of the stored procedure.

1st time using user defined tables so I may have made syntax errors

This will be so I can send a data table from a c# program instead of individually inserting each row.

Stored Procedure

CREATE PROCEDURE [dbo].[Upload_AddBulkProducts]
@uploadedTable CSV_ADDProducts readonly
AS
    INSERT INTO [dbo].[Products]
     SELECT * FROM @uploadedTable
GO

Error = undefined variable The error i get when trying to create the stored procedure

User Defined Table

CREATE TYPE [dbo].[CSV_ADDProducts] AS TABLE(
[Product Item] [nvarchar](50) NULL,
[Product SKU] [bigint] NULL,
[Product Name] [nvarchar](max) NULL,
[Product Active] [nchar](10) NULL,
[Product Selling Price] [money] NULL,
[Product Description] [nvarchar](max) NULL,
[Product Purchase Description] [nvarchar](max) NULL,
[Product VAT Code ID] [bigint] NOT NULL,
[Product Last Update] [datetime] NULL
)
GO

you cannot pass table name as parameter at all , unless you want to use dynamic sql — eshirvana
– eshirvana, Commented Nov 12, 2020 at 18:28

gotqn · Accepted Answer · 2020-11-12 19:20:22Z

1

In the context of T-SQL your code is valid. The type can be created and the procedure, too.

Then, in the C# code use the following:

DataTable dt = new DataTable();
dt.Columns.Add("Product Item", typeof (string));
//add other columns here

SqlParameter param = new SqlParameter("@uploadedTable", SqlDbType.Structured)
{
    TypeName = "dbo.userdefinedtabletype",
    Value = dt
};
sqlComm.Parameters.Add(param);

answered Nov 12, 2020 at 19:20

gotqn

44k47 gold badges167 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alan P Over a year ago

Thanks @gotqn , i just got it to work but not too sure how. would you know how to change the sp to T-SQL query?

Collectives™ on Stack Overflow

using user defined tables in SQL Stored Procedure - invalid data type

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related