Passing a result set to a stored procedure

Hi All,
I have sometimes used the following sort of query to pull data from one
table to another:
INSERT INTO Table1
SELECT fname, lname
FROM Table2
Now, let's suppose that I had created a stored procedure to do the
insert (and any other logic i was concerned about) and I did something
like this:
EXECUTE Table1 _Insert
SELECT fname, lname
FROM Table2
It won't work, giving an error that looks something like this:
Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0
Procedure 'Table1_Insert' expects parameter '@.fname', which was not
supplied.
I assume I'm not doing things right... how would I pass a result set to
a stored procedure, with each row corresponding to an input parameter
of the stored procedure?Hi
INSERT INTO TableName EXEC mySP
<joshbeall@.gmail.com> wrote in message
news:1144068457.520507.208200@.i40g2000cwc.googlegroups.com...
> Hi All,
> I have sometimes used the following sort of query to pull data from one
> table to another:
> INSERT INTO Table1
> SELECT fname, lname
> FROM Table2
>
> Now, let's suppose that I had created a stored procedure to do the
> insert (and any other logic i was concerned about) and I did something
> like this:
>
> EXECUTE Table1 _Insert
> SELECT fname, lname
> FROM Table2
>
> It won't work, giving an error that looks something like this:
>
> Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0
> Procedure 'Table1_Insert' expects parameter '@.fname', which was not
> supplied.
>
> I assume I'm not doing things right... how would I pass a result set to
> a stored procedure, with each row corresponding to an input parameter
> of the stored procedure?
>|||Your Table1_Insert proc is expecting a parameter. How about posting the
code for the proc?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<joshbeall@.gmail.com> wrote in message
news:1144068457.520507.208200@.i40g2000cwc.googlegroups.com...
Hi All,
I have sometimes used the following sort of query to pull data from one
table to another:
INSERT INTO Table1
SELECT fname, lname
FROM Table2
Now, let's suppose that I had created a stored procedure to do the
insert (and any other logic i was concerned about) and I did something
like this:
EXECUTE Table1 _Insert
SELECT fname, lname
FROM Table2
It won't work, giving an error that looks something like this:
Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0
Procedure 'Table1_Insert' expects parameter '@.fname', which was not
supplied.
I assume I'm not doing things right... how would I pass a result set to
a stored procedure, with each row corresponding to an input parameter
of the stored procedure?|||Do you mean that you want to pass a table through as an argument, in
which case you can use a table variable something like:
DECLARE @.table table(Col1 int, Col2 varchar(40))
INSERT INTO @.Table(Col1, Col2)
SELECT *
FROM table2
exec table1_insert @.Table
(untested).|||Will,
Table variable cannot be used as an input parameter
"Will" wrote:

> Do you mean that you want to pass a table through as an argument, in
> which case you can use a table variable something like:
> DECLARE @.table table(Col1 int, Col2 varchar(40))
> INSERT INTO @.Table(Col1, Col2)
> SELECT *
> FROM table2
> exec table1_insert @.Table
> (untested).
>|||lol - had a suspicion there was something wrong with that example
(hence the caveat (untested)).|||See the article:
http://www.sommarskog.se/share_data.html
Anith|||Tom Moreau wrote:
> Your Table1_Insert proc is expecting a parameter. How about posting the
> code for the proc?
> --
> Tom
The stored procedure is simply an INSERT statement. In this simplified
example there's no real reason you would have a stored procedure, but
I'm trying to figure out the method to handle this, so in the event
that you had a more substantial reason to use a stored procedure, you
could pass every row from a table to that stored procedure. Or more
specifically, you could select specific columns from another table, and
pass them to a stored procedure, one row at a time.
At any rate, here is the code for my stored procedure:
CREATE PROCEDURE DeleteMe_Insert
@.fname varchar(50),
@.lname varchar(50)
AS
INSERT INTO DeleteMe VALUES(@.fname, @.lname)|||The proc you have below actually has two parameters:
@.fname varchar(50),
@.lname varchar(50)
Thus, when you call it, you must feed it values for both of these
parameters. Therefore, this proc would be called like:
EXEC DeleteMe_Insert 'Joe', 'Smith'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<joshbeall@.gmail.com> wrote in message
news:1144082359.964230.311950@.u72g2000cwu.googlegroups.com...
Tom Moreau wrote:
> Your Table1_Insert proc is expecting a parameter. How about posting the
> code for the proc?
> --
> Tom
The stored procedure is simply an INSERT statement. In this simplified
example there's no real reason you would have a stored procedure, but
I'm trying to figure out the method to handle this, so in the event
that you had a more substantial reason to use a stored procedure, you
could pass every row from a table to that stored procedure. Or more
specifically, you could select specific columns from another table, and
pass them to a stored procedure, one row at a time.
At any rate, here is the code for my stored procedure:
CREATE PROCEDURE DeleteMe_Insert
@.fname varchar(50),
@.lname varchar(50)
AS
INSERT INTO DeleteMe VALUES(@.fname, @.lname)|||Will wrote:
> Do you mean that you want to pass a table through as an argument?
No, I want to pass each row to the stored procedure, separately.
Presumably what I would have to do is some sort of loop construct that
reads a row out of the source table, and passes it to the stored
procedure...?