在sql 2005中从sp调用sp [英] calling sp from sp in sql 2005

问题描述

我想从sql 2005中的存储过程中调用存储过程.
我的代码是

I want to call stored procedure from stored procedure in sql 2005.
My code is

create PROCEDURE dbo.SPonDeptTable
	(
	@Option int,
	@CompName varchar(50)
	)	
AS

	begin
	if @Option=0
	 begin
select DeptName from MstDepartment where CompanyID=(exec SPonCompanyTabl(1,@CompName)); 

 end
	RETURN
	end

SPonCompanyTabl是

and SPonCompanyTabl is

create PROCEDURE dbo.SPonCompanyTabl
    (
    @Option int,
    @name varchar
    )
AS

    begin
    --declare @CompID varchar(50)
    if @Option=0
     begin
select CompanyName from MstCompany

 end
 else if @Option=1
 begin
 select CompanyID from MstCompany where CompanyName=@name
 end
    RETURN
    end

SPonCompanyTabl工作正常，但是当我创建SPonDeptTable时，错误是

关键字"exec"附近的语法不正确
关键字"1"附近的语法不正确

SPonCompanyTabl is working fine but when I create SPonDeptTable error is

incorrect syntex near the keyword ''exec''
incorrect syntex near the keyword ''1''

推荐答案

该过程未返回值，因此可以改用函数.有关更多信息，请参见创建功能 [

The procedure doesn''t return a value so you could use a function instead. For more information see CREATE FUNCTION[^].

Of course one possibility would be to combine the procedures for example something like:

create PROCEDURE dbo.SPonDeptTable
	(
	@Option int,
	@CompName varchar(50)
	)	
AS
BEGIN
   IF @Option=0 BEGIN
      SELECT DeptName 
      FROM   MstDepartment 
      WHERE  CompanyID = (SELECT CompanyID 
                          FROM   MstCompany 
                          WHERE  CompanyName=@CompName); 
 
   END
   RETURN
END

这篇关于在sql 2005中从sp调用sp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！