INSERT SELECT with Stored Procedures?

[John Bailo]

A cool feature of SQL is that I can use a SELECT statement and pass
values into an INSERT -- great for moving a few records between tables.

Question, is there a way to use a SELECT to pass values into a stored
procedure?

 

[Dave Sexton]

Hi John,

AFAIK, no (unfortunately).

 

[Garfilone]

ye,of cource you can do that

sample SP shows as below:

CREATE PROCEDURE ShowAFile
@FileNo nvarchar(50)
as
select c.FileNO,
c.Name,
c.Sex ,
c.Birthday,
e.RaceName,
c.Political,
c.PriQualify,
c.PriGradSchool,
c.PriGradDate,
c.Qualify,
c.GradSchool,
c.GradDate,
b.DutyName ,
f.TitleName,
d.JobName,
a.DeptName,
c.WorkDate,
c.EnterDate,
c.OrgDateé—´,
c.Origin,
c.Resume
from Departments a,Duties b,Files c,Jobs d,Races e,Titles f
where a.DeptID=c.DeptID and b.DutyID=c.DutyID and d.JobID=c.JobID and
e.RaceID=c.RaceID
and f.TitleID=c.TitleID
and c.FileNo=@FileNo
GO

 

[Chris Dunaway]

ye,of cource you can do that

sample SP shows as below:

<snip>

That's not what he asked. He wants the result set from the Select
statement to be passed INTO the stored procedure. In SQL you can do
this:

INSERT Into tablename SELECT * From whatever

This uses the results of the select as the field values for the insert.
I believe the OP wants to do a similar action using a stored procedure
instead of an insert statement:

Something like this (pseudocode to illustrate point):

EXEC spStoredProcName SELECT * From Whatever

Which is not possible AFAIK.

Chris