question

KrampMarisa-1166 avatar image
0 Votes"
KrampMarisa-1166 asked ·

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

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

sql-server-transact-sql
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Have you tried the methods provided by the experts? if they do not work, please provide more details.

Regards
Echo

0 Votes 0 ·
lukasz-przyjemski avatar image
0 Votes"
lukasz-przyjemski answered ·

Could you please describe more precisely what you want to get? Is the result of the procedure execution a table or a single value?

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered ·

Hi @KrampMarisa-1166,

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:
77622-image.png

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:
77516-image.png


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.



image.png (9.0 KiB)
image.png (9.2 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered ·

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.

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Well, it could be done with a default on the column in question.

But given how brief the initial question is, and the fact that the poster has not come back, there is little reason to waste time on this question.

1 Vote 1 ·