Is it possible to do an Insert Into stored procedu

问题:

Basically I have a table of data and I would like to take the values of each row and feed it to the stored procedure.

In my perfect world, I'd do this

insert into StoredProcA @var1 @var2
select testdesc, testoption
from tableA
where testoption = 1

Well, I don't think that's going to work. So how, if possible, can I take the all data from a table/query and pass it to the stored procedure?

EDIT: The stored procedure already exists and does quite a bit of processing to the incoming data. The amount of data from the source table itself is only 300 rows.

回答1:

You will need to do a couple of things to get this going, since your parameter is getting multiple values you need to create a Table Type and make your store procedure accept a parameter of that type.

Since you are passing a TABLE as a parameter you will need to create a TABLE TYPE something as follows

TABLE TYPE

CREATE TYPE dbo.Prco_Table AS TABLE 
 (
    [Val1]         Data Type
    [Val2]         Data Type
  )
 GO

Stored Procedure to Accept That Type Param

 CREATE PROCEDURE mainValues 
 @TableParam Prco_Table READONLY   --<-- Accepts a parameter of that type 
 AS                                  -- Note it is ReadOnly 
 BEGIN
    SET NOCOUNT ON;
  /* do your insert from this parameter or other cool stuff  */

  INSERT INTO Target_Table (Col1, Col2)
  SELECT [Val1] , [Val2]
  FROM  @TableParam    --<-- Table Type variable


END

EXECUTE PROC

Declare a variable of that type and populate it with your values.

 DECLARE @Table ClaimData(      --<-- Declare a variable of your type
          [Val1]         Data Type
          [Val2]         Data Type
                         ); 

 -- Populate the variable
   INSERT INTO @Table ([Val1],[Val2])
   SELECT testdesc, testoption
   FROM tableA
   WHERE testoption = 1

  EXECUTE mainValues  @Table --<-- Pass this variable of Table Type

回答2:

I ended up using @logixologist suggestion and used a cursor.

It worked quite well.

declare testCursor cursor
for
select testdesc, testoption
from tableA
where testoption = 1

open testCursor
declare @p1 varchar(max), @p2 varchar(8)

fetch next from testCursor into @p1, @p2
while (@@fetch_status <> -1)
begin
   if (@fetch_status <> -2)
   exec db1.dbo.usr_storedproc @p1, @p2
   fetch next from testCursor into @p1, @p2
end
close testCursor
deallocate testCursor
go

If anyone has any improvements, better way, or different way of doing this, please post solution as an answer.