T-SQL out of order insert

Question

Basically I have a User-Defined Table Type (for use as a table-valued variable) which I'm referencing in a stored procedure that effectively just calls two other stored procedures, then inserts those values into the table type.

Id est

INSERT INTO @tableValuedVariable (var1, var2, var3, var4, var5)
         EXEC [dbo].StoredProcedure1;

INSERT INTO @tableValuedVariable (var1, var2, var5)
         EXEC [dbo].StoredProcedure2;

You can probably already tell what I'm going to ask. Basically StoredProcedure2 only returns a few of the values the table is set to hold, and I'd like those other variables to just be null (as defined as default). Only SQL is complaining that I'm not specifying all the variables available to that table.

The return datasets can be quite sizable so I'd like to avoid loops and such for obvious reasons.

Thanks for any help.

Answer 1

You have list all columns in the INSERT clause to match all columns from the stored proc.

Now, if the stored proc returns var1, var2, var5 then there should be no need to specify var3, var4 in the INSERT clause if they are NULLable. (Edit: of course, as Mike Burton pointed out, it will fail if the columns are NOT NULL)

If the stored proc returns var1, var2, var3, var4, var5 then you need all 5 in the INSERT clause: you can't ignore columns from the stored proc and allow them to default.

You could change the stored proc to a udf and SELECT var1, var2, var5 FROM mynewUDF()

Answer 2

This works for me:

CREATE PROCEDURE prc_test_1
AS
        SELECT  1 AS id, 2 AS id2

GO

CREATE PROCEDURE prc_test_2
AS
        SELECT  1 AS id

GO

DECLARE @tv TABLE (id INT, id2 INT)

INSERT
INTO    @tv
EXEC    prc_test_1

INSERT
INTO    @tv (id)
EXEC    prc_test_2

SELECT  *
FROM    @tv