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?
Have you tried the methods provided by the experts? if they do not work, please provide more details.
Regards
Echo
Could you please describe more precisely what you want to get? Is the result of the procedure execution a table or a single value?
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.
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.
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.
10 people are following this question.