CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Mable98
Mable98

Reputation: 41

Can a Table-Type parameter be defined in the definition of a Stored Procedure

Is it possible to define a table type as a parameter in the definition of a stored procedure?

Something like this:

CREATE PROCEDURE [dbo].[proc_People_insert]
      @People TABLE(Name VARCHAR(20), Age INT) READONLY
AS
    -- Do something here
GO

I know I can first create a Table Type like:

 CREATE TYPE [dbo].Person AS TABLE
 (
    [Name] VARCHAR(20), 
    Age INT
 )

and then define the procedure like

CREATE PROCEDURE [dbo].[prco_People_insert]
      @People dbo.Person READONLY
AS
    -- Do something here
GO

But this will leave me with a bunch of user-defined Table Types hanging around in the Database.

I am looking to use Dapper to call the stored proc and pass in a list of Person types.

I can achieve the bulk insert functionality with an inline SQL statement and Dapper (see below) but am wondering whether the same can be acheived with stored procs and Dapper. Something like...

INSERT INTO dbo.People([Name], Age)
VALUES(@Name, @Age)

// C# Person class
public class Person(public string Name, public int Age)

// Pseudo Dapper call
connection.Execute(sql, new List<Person>(){ ... });

Upvotes: 0

Views: 844

Answers (1)

Mable98
Mable98

Reputation: 41

As per @DanGuzman 's suggestion, passing in JSON works a treat

CREATE PROCEDURE [dbo].[prc_PeopleByJSON_insert]
    @PeopleJSON VARCHAR(MAX)
AS
    INSERT INTO PersonTable(Name, Age)
    SELECT Name, Age
    FROM OPENJSON(@PeopleJSON)
    WITH(Name VARCHAR(20), Age INT)
GO

Upvotes: 1

Related Questions