Trouble inserting into table with table valued parameter

Question

I created a table type:

CREATE TYPE int_array AS TABLE (n INT NOT NULL)

and want to use it to insert several rows in a single procedure into the following table:

CREATE TABLE myTable
(
    Id_SecondTable INT NOT NULL,
    Id_ThirdTable  INT NOT NULL,

    CONSTRAINT PK_myTable 
        PRIMARY KEY (Id_SecondTable, Id_ThirdTable),
    CONSTRAINT FK_Id_SecondTable 
        FOREIGN KEY (Id_SecondTable) REFERENCES SecondTable (Id),
    CONSTRAINT FK_Id_ThirdTable 
        FOREIGN KEY (Id_ThirdTable) REFERENCES ThirdTable (Id)
)

My procedure is as follows:

CREATE PROCEDURE test 
     (@value INT, 
      @array int_array READONLY)
AS 
BEGIN
    INSERT INTO myTable 
    VALUES (Id_SecondTable, Id_ThirdTable) (@value, SELECT n FROM @array)   
END 

What am I doing wrong?

Answer 1

You can't mix a scalar value and select statement in your insert. You need to make the scalar value a column in the select instead. Like this.

CREATE PROCEDURE test(
@value INT, 
@array int_array readonly 
)
AS 
BEGIN

INSERT INTO myTable 
(
    Id_SecondTable
    , Id_ThirdTable
) 
SELECT @value
    , n 
FROM @array
END