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...?

pass data between two datasets

Is there anyway i can pass data between two datasets
ex:
dataset 1 has
select empid, fname,lname ,salary from employee
dataset2 has
select userid ,total/(dataset1.salary) from anothertable
here i want to use a value from dataset 1 in dataset 2.
Thanks in advnceLook into using subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ramani" <Ramani@.discussions.microsoft.com> wrote in message
news:DECA721E-7B10-4D19-B57C-BA92D951453F@.microsoft.com...
> Is there anyway i can pass data between two datasets
> ex:
> dataset 1 has
> select empid, fname,lname ,salary from employee
> dataset2 has
> select userid ,total/(dataset1.salary) from anothertable
> here i want to use a value from dataset 1 in dataset 2.
> Thanks in advnce|||Sub reports ? but i dont want to go into the front end. i want to do it in
the backend itself.
you know pass data from one data set to another directly
Thanks
"Bruce L-C [MVP]" wrote:
> Look into using subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ramani" <Ramani@.discussions.microsoft.com> wrote in message
> news:DECA721E-7B10-4D19-B57C-BA92D951453F@.microsoft.com...
> > Is there anyway i can pass data between two datasets
> >
> > ex:
> > dataset 1 has
> > select empid, fname,lname ,salary from employee
> >
> > dataset2 has
> > select userid ,total/(dataset1.salary) from anothertable
> >
> > here i want to use a value from dataset 1 in dataset 2.
> >
> > Thanks in advnce
>
>|||You can embed a sub report into the table of the first report passing it the
value from the field of the first one (the subreport should have a
parameter).
There is no way from within RS to tie one dataset to another. Either use
subreports or put the logic in a stored procedure (but since a stored
procedure can only return a single resultset, or rather RS will only use a
single resultset from a stored procedure) I don't think a stored procedure
will work for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ramani" <Ramani@.discussions.microsoft.com> wrote in message
news:B4ED0F7F-4CCE-4484-B21F-8DD2B84EF0A3@.microsoft.com...
> Sub reports ? but i dont want to go into the front end. i want to do it in
> the backend itself.
> you know pass data from one data set to another directly
> Thanks
> "Bruce L-C [MVP]" wrote:
>> Look into using subreports.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ramani" <Ramani@.discussions.microsoft.com> wrote in message
>> news:DECA721E-7B10-4D19-B57C-BA92D951453F@.microsoft.com...
>> > Is there anyway i can pass data between two datasets
>> >
>> > ex:
>> > dataset 1 has
>> > select empid, fname,lname ,salary from employee
>> >
>> > dataset2 has
>> > select userid ,total/(dataset1.salary) from anothertable
>> >
>> > here i want to use a value from dataset 1 in dataset 2.
>> >
>> > Thanks in advnce
>>|||Ramani,
You really need to get all the data you want for a data region into one
dataset. You need to redo the query, there must be a relationship
between ds1 and ds2, or if it's just a continuous list of data try the
Union command.
Sub-reports require much greater overhead it's better not to use them
in this scenario.
Maybe try something like this;
Select e.empid, e.fname, e.lname, e.salary, a.userid, a.total
from employee e
join anothertable a on e.empid = a.userid
It may generate lots of repeated data, but RS can deal with this much
more efficiently than using sub-reports.
Chris
Ramani wrote:
> Is there anyway i can pass data between two datasets
> ex:
> dataset 1 has
> select empid, fname,lname ,salary from employee
> dataset2 has
> select userid ,total/(dataset1.salary) from anothertable
> here i want to use a value from dataset 1 in dataset 2.
> Thanks in advnce|||Easiest way may be to create a view between the 2.
"Ramani" wrote:
> Is there anyway i can pass data between two datasets
> ex:
> dataset 1 has
> select empid, fname,lname ,salary from employee
> dataset2 has
> select userid ,total/(dataset1.salary) from anothertable
> here i want to use a value from dataset 1 in dataset 2.
> Thanks in advnce