Insert results of a table into stored procedure as parameters

Question

I have a stored procedure which inserts values into a table. Let's say its name is usp_InsertTableA with parameters @ID int and Name varchar(100).

I have a requirement to call this stored procedure multiple times from another stored procedure. I am thinking to call this stored procedure something like below

exp usp_InsertTableA
select ID, Name from #tempTable

Is this possible in SQL Server to execute this with the value of the table and send it into a stored procedure?

Answer 1

You can use table type parameters to stored procedure.

CREATE TYPE [dbo].[udt_MyCustomTable] AS TABLE(
    [id] [int] NOT NULL,
    [name] [nvarchar](100) NOT NULL
)
GO

And then you stored procedure would be:

CREATE PROC [dbo].[usp_InsertTableA]
 (
    @myCustomTable udt_MyCustomTable READONLY
 )
 AS
BEGIN
  -- Your code goes in here
END