Call a stored procedure from within an INSERT INTO

Question

So I have this stored procedure that takes in a DATETIME parameter, date. It inserts that value into another table and returns that ID.

Lets call this stored procedure getTimeID. Where DimTime is the table it inserts the DATETIME parameter into.

So I want to copy into TableB ID and TimeID, not ID and date.

I'm trying:

INSERT INTO  [TableA].dbo.Main (ID, timeID) 
SELECT  ID,
        (EXEC getTimeID date)
FROM      [TableB].dbo.Main

But I cannot get the syntax on EXEC to work.

Please help.

Solution

The syntax for inserting from a stored procedure is:

INSERT INTO TheTable (col1, col2) EXEC TheStoredProcedure

It's very inflexible: the table has to match the exact column layout of the stored procedure.

One possible solution would be to store the result from the stored procedure into a table variable. For example:

declare @id int
select  @id = ID 
from    [TableB].dbo.Main

declare @t table (TimeID int)
insert @t (TimeID) exec getTimeID '2011-01-20'

declare @TimeID int
select  @TimeID = TimeID 
from    @t

insert [TableA].dbo.Main values (@ID, @TimeID)