Passing a result set to a stored procedure

3 Apr 2006 12:47 PM

joshbeall

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?

3 Apr 2006 12:50 PM

Uri Dimant

Hi
INSERT INTO TableName EXEC mySP

<joshbe***@gmail.com> wrote in message
Show quote

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

3 Apr 2006 12:51 PM

Tom Moreau

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
..
<joshbe***@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?

3 Apr 2006 4:39 PM

joshbeall

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)

3 Apr 2006 4:42 PM

joshbeall

Actually I had some variable names name (specifically, sp name and
table name)...

CREATE PROCEDURE Table1_Insert
@fname varchar(50),
@lname varchar(50)
AS
INSERT INTO Table1 VALUES(@fname, @lname)

DeleteMe is a nomenclature I use sometimes to specify something (e.g.,
an sp) that I created only for a moment, and that should be deleted
immediately and has no use or function.

3 Apr 2006 4:45 PM

Tom Moreau

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
..
<joshbe***@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)

3 Apr 2006 12:51 PM

Will

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

3 Apr 2006 1:01 PM

Omnibuzz

Will,
Table variable cannot be used as an input parameter

Show quote

"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).
>
>

3 Apr 2006 1:07 PM

Will

lol - had a suspicion there was something wrong with that example
(hence the caveat (untested)).

3 Apr 2006 4:51 PM

joshbeall

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

3 Apr 2006 5:06 PM

Tom Moreau

Why loop? You can simply do an INSERT SELECT:

INSERT MyTable
SELECT
*
FROM
MyOtherTable

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<joshbe***@gmail.com> wrote in message

news:1144083068.512621.267040@g10g2000cwb.googlegroups.com...
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...?

3 Apr 2006 5:26 PM

joshbeall

Tom Moreau wrote:

> Why loop? You can simply do an INSERT SELECT:
>
> INSERT MyTable
> SELECT
> *
> FROM
> MyOtherTable

Because I need to use a stored procedure in my case.

What you are suggesting is the same sort of thing that I want to do,
but I want to use a stored procedure, rather than simplying passing it
to an INSERT statement.

In my simple example, you are correct, there is no reason to use an sp,
you might as well use a regular INSERT statement. However, my actual
situation is that I need to use a stored procedure to handle what I'm
working on. I simply was trying to give a very simple example of the
sort of thing I want to do. The details of my stored procedure are not
important. I just want to know how I can get a result set from a
SELECT, and then use the results returned (one per row) and pass them
to a stored procedure.

Conceptually, this is what I want to do:

result = SELECT fname, lname FROM Table1;
foreach (row in result)
EXECUTE Table2_insert row['lname'], row['fname'];

3 Apr 2006 5:32 PM

Tom Moreau

Basically, you can't pass the resultset to a proc. What you can do, though,
is store the results in a table - be it a permanent work table or a temp
table. The proc can then access those rows.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<joshbe***@gmail.com> wrote in message

news:1144085166.067788.307470@e56g2000cwe.googlegroups.com...

Tom Moreau wrote:

> Why loop? You can simply do an INSERT SELECT:
>
> INSERT MyTable
> SELECT
> *
> FROM
> MyOtherTable

Because I need to use a stored procedure in my case.

What you are suggesting is the same sort of thing that I want to do,
but I want to use a stored procedure, rather than simplying passing it
to an INSERT statement.

In my simple example, you are correct, there is no reason to use an sp,
you might as well use a regular INSERT statement. However, my actual
situation is that I need to use a stored procedure to handle what I'm
working on. I simply was trying to give a very simple example of the
sort of thing I want to do. The details of my stored procedure are not
important. I just want to know how I can get a result set from a
SELECT, and then use the results returned (one per row) and pass them
to a stored procedure.

Conceptually, this is what I want to do:

result = SELECT fname, lname FROM Table1;
foreach (row in result)
EXECUTE Table2_insert row['lname'], row['fname'];

4 Apr 2006 3:06 PM

joshbeall

I got a reply from someone at forums.asp.net that explained a method to
do this using temporary tables:

DECLARE @t table (RowId int IDENTITY(1,1), col1 varchar(10), col2
varchar(10) ....)
DECLARE @rowid int

INSERT INTO @t (col1, col2,...)
SELECT....FROM...WHERE

WHILE EXISTS (SELECT * FROM @t)
BEGIN
      --Initialize. this is impotant so values from previous loop dont
      --get carried over.
      SELECT @val2 = NULL, @val2=NULL
      --get the row id
   SELECT @rowid = MIN(rowId) FROM @t
      --get the values for that row
   SELECT @val=col1, @val2=col2 FROM @t WHERE rowid = @rowID
   --call the stored proc with those values
   EXEC Insert_storedproc @val1, @val2
   --delete the row from the temp table
      --This is important to avoid infinite loops.
   DELETE FROM @t WHERE rowId= @rowId
END

4 Apr 2006 4:15 PM

Will

I might be missing something here, but isn't that just a really duff
implementation of a cursor?

I think Tom had the right idea in suggesting you populate a temporary
table (perhaps it will have to be a global one depending on your actual
situation), then use that to perform the operations functionally on the
whole set within your sp. Otherwise...

DECLARE duffCursor cursor
FOR
SELECT col1, col2
FROM myTable

DECLARE @Var1 int
DECLARE @Var2 int

OPEN duffCursor

FETCH NEXT FROM duffCursor INTO @Var1, @Var2
WHILE @@FETCH_STATUS=0
BEGIN

exec mysp @Var1, @Var2
FETCH NEXT FROM duffCursor INTO @Var1, @Var2

END
CLOSE duffCursor
DEALLOCATE duffCursor

The advantage of doing this is not only that you don't need to maintain
your table, but if you need to optimise it you could add hints like
FAST_FORWARD, and FORWARD ONLY, which will go quicker

4 Apr 2006 4:36 PM

Will

Though I'll point out before everyone pounces - you can't use both
Fast_Forward and Forward_Only together (lol - bet I'm wrong about this
and will in fact now receive the kicking for it)

6 Apr 2006 12:47 PM

Prasad

Will,
Create a stored procedure containing a Select statement to insert data into
a table. When Select is used in this way the result set generated by stored
proc SELECT statement is loaded into the table.

Synatax...

INSERT [INTO]
{table_name} [(column_list)]
   EXECute { procedure_name | @procedure_name_var}
   [[@parameter_name=] {value | @variable [OUTPUT] | DEFAULT}
   [, [@parameter_name =] {value | @variable [OUTPUT] |
DEFAULT}]...]

Note:
The number & type of the returned fields must match the layout of the table
given in insert.

Example:

USE Northwind
GO

CREATE TABLE [dbo].[EMP] (
    [EmployeeID] [int] NULL ,
    [LastName] [nvarchar] (20) NULL
) ON [PRIMARY]
GO

--Create Select Stored Proc
CREATE PROCEDURE [DBO].[EMP_Select]
AS
    SELECT EmployeeID, LastName
    FROM   Employees

GO

--Insert Values using Stored Proc
INSERT INTO [EMP]([EmployeeID], [LastName])
-- Execute Select Stored Procedure
EXEC [EMP_Select]
GO

Hope this helps.

Happy Coding!!!

Thanks,
Prasad

Show quote

"Will" wrote:

> Though I'll point out before everyone pounces - you can't use both
> Fast_Forward and Forward_Only together (lol - bet I'm wrong about this
> and will in fact now receive the kicking for it)
>
>

6 Apr 2006 1:00 PM

Prasad

Using the nested stored proc option

CREATE PROCEDURE [DBO].[EMP_Select]
AS
SELECT   EmployeeID, LastName
FROM         Employees
GO

CREATE PROCEDURE [DBO].[EMP_Insert]
AS
INSERT INTO [EMP]([EmployeeID], [LastName])
EXEC [EMP_Select]
GO

Happy Coding!!!

Thanks,
Prasad

Show quote

"Prasad" wrote:

> Will,
> Create a stored procedure containing a Select statement to insert data into
> a table. When Select is used in this way the result set generated by stored
> proc SELECT statement is loaded into the table.
>
> Synatax...
>
> INSERT [INTO]
> {table_name} [(column_list)]
>    EXECute { procedure_name | @procedure_name_var}
>    [[@parameter_name=] {value | @variable [OUTPUT] | DEFAULT}
>    [, [@parameter_name =] {value | @variable [OUTPUT] |
> DEFAULT}]...]
>
> Note:
> The number & type of the returned fields must match the layout of the table
> given in insert.
>
> Example:
>
> USE Northwind
> GO
>
> CREATE TABLE [dbo].[EMP] (
>     [EmployeeID] [int] NULL ,
>     [LastName] [nvarchar] (20) NULL
> ) ON [PRIMARY]
> GO
>
>
> --Create Select Stored Proc
> CREATE PROCEDURE [DBO].[EMP_Select]
> AS
>     SELECT EmployeeID, LastName
>     FROM   Employees
>
> GO
>
> --Insert Values using Stored Proc
> INSERT INTO [EMP]([EmployeeID], [LastName])
> -- Execute Select Stored Procedure
> EXEC [EMP_Select]
> GO
>
> Hope this helps.
>
> Happy Coding!!!
>
> Thanks,
> Prasad
>
>
> "Will" wrote:
>
> > Though I'll point out before everyone pounces - you can't use both
> > Fast_Forward and Forward_Only together (lol - bet I'm wrong about this
> > and will in fact now receive the kicking for it)
> >
> >

3 Apr 2006 1:17 PM

Anith Sen

See the article:
http://www.sommarskog.se/share_data.html

--
Anith