如何从SQL Server中的另一个存储过程调用存储过程？

I have a main stored procedure that insert 3 varchar fields, name, address and phone number and other 3 fields that insert an ID from foreign keys in a table, these 2 foreign key fields also use an insert stored procedure to insert their own data. How do I call those stored procedures from the main SP to insert the varchar fields and the foreign key fields generated when executing them?

我有一个主存储过程,插入3个varchar字段,名称,地址和电话号码以及其他3个字段,这些字段在表中插入外键中的ID,这两个外键字段也使用插入存储过程来插入自己的数据。如何从主SP调用这些存储过程来插入varchar字段和执行时生成的外键字段?

Table 1          Table 2         Table 3
id,              id2,            id3,
name1,           name2,          name3, 
address1,        address2,       address3,
phoneNumber1,    phoneNumber2,   phoneNumber3
id2,
id3,

create procedure table1    
@id int,
@name1 varchar(30),
@address1 varchar(100),
@phoneNumber1 varchar(30)
as
    BEGIN
set nocount on;

Insert into table1
           (id,
           name,
           address,
           phoneNumber)
    values(
           @id,
           @name,
           @address,
           @phoneNumber)
    END

------After inserting the data into table 1, I want to call the SP of table 2 and 3---

EXEC SPTable2

1 个解决方案

#1

From the main procedure:

从主要程序:

Insert into table1 (...)
exec yourProcName @param='foo'

You should have each proc return the data set you need and the main proc call each of them and do an INSERT INTO as I showed above.

您应该让每个proc返回您需要的数据集,并且主proc调用它们中的每一个并执行INSERT INTO,如上所示。

The table definition of table1 has to match exactly the number of columns and data types of the result sets returned by the other procs.

table1的表定义必须与其他procs返回的结果集的列数和数据类型完全匹配。

If you can't guarantee this condition, you are probably better off writing a function that returns a table so that you can do a insert into table1 (....) select col1, col2, etc from fnFoo(@param)

如果你不能保证这个条件,你可能最好写一个返回表的函数,这样你就可以插入table1(....)从fnFoo中选择col1,col2等(@param)

#1