嵌套存储过程创建问题 [英] Nested Stored Procedure Creation Problem

问题描述

我需要创建嵌套的SP，我使用以前的SP在自己的SP中执行.但这会引发错误...

请给我一些有用的信息以解决此问题....

i need to create nested SP, i used previous SP to execute inside my Own SP. But it throws error...

Please give me some Productive input to fix this mess....

create procedure ALLinone
(
@Sname varchar(50),
@Srollno varchar(50),
@Pname varchar(50),
@Saddr varchar(50)
)
As
Begin
Exec SP_Testone @Sname, @Srollno
Go
Exec SP_Testtwo @Sname, @Pname
Go
Exec SP_Testthree @Srollno, @Saddr
End

它会引发类似...的错误

It throws error like...

Msg 102, Level 15, State 1, Procedure ALLinone, Line 11<br />
Incorrect syntax near ''@Srollno''.<br />
Msg 137, Level 15, State 2, Line 2<br />
Must declare the scalar variable "@Sname".<br />
Msg 137, Level 15, State 2, Line 2<br />
Must declare the scalar variable "@Srollno".

甚至我都尝试单独运行每个SP，它的工作也很完美....但是问题是，在创建此嵌套SP

Even i tried run each and every SP seperately its works Perfect....But problem is while create this nested SP

推荐答案

GO时，这不是TSQL命令.它标志着查询分析器中批处理的结束，
因此，用信号通知该批处理中存储过程定义的结尾，所以
它不应该是SP的一部分.

注意:我假设三个调用是相互独立的，这就是为什么错误处理是分开的...

GO isn''t a TSQL command. It marks the end of a batch in Query Analyzer and
therefore signals the end of a stored procedure definition in that batch, so
it should not be part of an SP.

NOTE : I assumed that three calls are independent of each other and that is why error handling is seperate...

CREATE PROCEDURE ALLinone
(
	@Sname varchar(50),
	@Srollno varchar(50),
	@Pname varchar(50),
	@Saddr varchar(50)
)
AS
BEGIN
	CREATE TABLE #Errors (ErrAt NVARCHAR(20), ErrNumber INT, ErrorMessage NVARCHAR(MAX))
	
	BEGIN TRY
		EXEC SP_Testone @Sname, @Srollno
	END TRY
	BEGIN CATCH
		INSERT #Errors
		SELECT ''SP_Testone'', ERROR_NUMBER(), ERROR_MESSAGE()
	END CATCH;
	
	BEGIN TRY
		EXEC SP_Testtwo @Sname, @Pname
	END TRY
	BEGIN CATCH
		INSERT #Errors
		SELECT ''SP_Testtwo'', ERROR_NUMBER(), ERROR_MESSAGE()
	END CATCH;
	
	BEGIN TRY
		EXEC SP_Testthree @Sname, @Pname
	END TRY
	BEGIN CATCH
		INSERT #Errors
		SELECT ''SP_Testthree'', ERROR_NUMBER(), ERROR_MESSAGE()
	END CATCH;
	
	SELECT * FROM #Errors
END

这篇关于嵌套存储过程创建问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！