是否可以执行 Insert Into 存储过程? [英] Is it possible to do an Insert Into stored procedure?
问题描述
基本上我有一个数据表,我想获取每一行的值并将其提供给存储过程.
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:存储过程已经存在,并对传入的数据进行了大量处理.源表本身的数据量只有300行.
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.
推荐答案
您需要做一些事情来实现这一点,因为您的参数正在获取多个值,您需要创建一个表类型并制作您的存储过程接受该类型的参数.
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.
由于您将 TABLE
作为参数传递,因此您需要创建一个 TABLE TYPE,如下所示
Since you are passing a TABLE
as a parameter you will need to create a TABLE TYPE something as follows
表格类型
CREATE TYPE dbo.Prco_Table AS TABLE
(
[Val1] Data Type
[Val2] Data Type
)
GO
接受该类型参数的存储过程
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
执行过程
声明一个该类型的变量并用您的值填充它.
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
这篇关于是否可以执行 Insert Into 存储过程?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!