How to insert a constant value along with the results of a store procedure in a temp table?

Question

How to insert a constant value along with the results of a store procedure in a temp table?

Answer 1

Hi @Kramp Marisa ,

Welcome to the Microsoft TSQL Q&A Forum!
If the result returned by the stored procedure is a table,you can refer to the following example:

    create table student  
    (stuID char(10) primary key,  
    stuName varchar(30) not null,  
    major varchar(50) not null)  
    create table book  
    (BID char(10),  
    Title char(50),  
    Author char(20) )  
    create table borrow  
    (borrowID char(10),  
    BstuID char(10),  
    BID char(10),--Book number  
    Ttime datetime,--Borrowing date  
    Btime datetime,--Return date  
     )  
    insert into student values(1001,'linlin','Computer'),  
                              (1002,'Poplar','Computer'),  
       (1003,'Eyre','English'),  
                              (1004,'Julie','Business Administration'),  
       (1005,'linlin','Mathematic')  
    insert into book values('B001','Jane Eyre','charlotte browntie'),  
                           ('B002','Wuthering Heights','Emily Jane Bronte'),  
                           ('B003','The Lady of the Camellias ','Alexandre Dumas fils'),  
                           ('B004','The Wretched','Victor Hugo'),  
        ('B005','Notre Dame Cathedral','Victor Hugo')  
    insert into borrow values('T001','1001','B001','2007-12-26',NULL),  
                             ('T002','1004','B003','2008-1-5',NULL),  
                             ('T003','1005','B001','2007-10-8','2007-12-25'),  
      ('T004','1005','B002','2007-12-16','2008-1-7'),  
                             ('T005','1002','B004','2007-12-22',NULL),  
      ('T006','1005','B005','2008-1-6',NULL),  
                             ('T007','1002','B001','2007-9-11',NULL),  
                             ('T008','1005','B004','2007-12-10',NULL),  
                             ('T009','1004','B005','2007-10-16','2007-12-18'),  
                             ('T010','1002','B002','2007-9-15','2008-1-5'),  
                             ('T011','1004','B003','2007-12-28',NULL),  
                             ('T012','1002','B003','2007-12-30',NULL)  
    --Stored procedure     
    if exists (select * from sysobjects where name='b_stu')  
    drop procedure b_stu  
    go  
    create procedure b_stu  
    @ID char(10)   
    as  
    select stuName=(select stuName from student where stuID=borrow.BstuID ),  
           Title=(select Title from book where BID=borrow.BID), Ttime=Ttime,Btime=Btime   
        from borrow where BstuID=@ID  
          
    execute b_stu @ID='1005'  

The output of the stored procedure:

Insert the results of the process into a temporary table:

create table #Test(col1 char(25),col2 char(25),col3 datetime, col4 datetime)  
  
insert into #Test EXEC b_stu @ID='1005'  
  
select * from #Test  

The output of the temporary table:

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I assume you are talking about the construct:
INSERT INTO {table}
EXEC stored_proc;

If so, you cannot do what you are asking in a single step. You cannot change the fields you are inserting in this construct.

You would need to insert the values into a table, and then add your static data as a separate step.