将存储过程的输出分配给第二个SP内的另一个SP的变量 [英] Assigning the output of a stored procedure to the variable of another SP inside the second SP

问题描述

我正在使用sql server，我必须按照以下步骤操作：

Hi,
I'm using sql server and I have to procedures as follows:

--InsertLog
CREATE PROCEDURE 
InsertLog(
	@menuName nvarchar(500),
	@buttonName nvarchar(500),
	@buttonText nvarchar(500),
	@buttonPath nvarchar(500),
	@buttonCaptionPath nvarchar(500),
	@clickDateTime DateTime,
	@UserID nvarchar(500)-- Gets 7 items
	)
AS
BEGIN
DECLARE @linage nvarchar(500)
SET @linage = 
EXEC GetLinage @menuName,@linage
INSERT INTO 
LogTable(
	GUID,
	FormName,
	Linage,
	ButtonPath,
	ButtonCaptionPath,
	ButtonName,
	ButtonText,
	ClickDateTime,
	UserID 
	)
VALUES(
	newid(),
	@menuName,
	@linage,
	@buttonPath,
	@buttonCaptionPath,
	@buttonName,
	@buttonText,
	@clickDateTime,
	@UserID 
	)-- Saves 8 Items
END
GO

--GetLinage 
CREATE PROCEDURE GetLinage (
    @formidentity nvarchar(200),
    @linage  nvarchar(200) OUTPUT
)
AS
SELECT @linage = Linage
FROM formslist
WHERE (formslist.formID = @formidentity)
GO

GetLinage SP必须返回（或返回）的值不会被分配给变量InsertLog。



我无法找出它为什么不起作用，虽然我正在寻找并尝试不同的方法使它工作。



我尝试过的事情：



我正在努力，但我还没有找到一个好的解决方案。

The value which the GetLinage SP must return (or returns) does not get assigned to the variable in the InsertLog.

I cannot find out why it doesn't work, though I'm searching and trying different ways to make it work.

What I have tried:

I'm working on it but yet I haven't found a good solution.

推荐答案

首先，你错过了调用程序时参数上的 OUTPUT 修饰符。



其次，你将覆盖变量使用存储过程的返回值。由于 GetLinage 过程不包含任何 Return 语句，因此返回值为 0 。



删除 SET @linage = 部分，并添加 OUTPUT 修饰符：

Firstly, you're missing the OUTPUT modifier on the parameter when you call the procedure.

Secondly, you're then overwriting the variable with the return value of the stored procedure. Since your GetLinage procedure doesn't include any Return statements, the return value is 0.

Remove the SET @linage = part, and add the OUTPUT modifier:

DECLARE @linage nvarchar(500);
EXEC GetLinage @menuName, @linage = @linage OUTPUT;

这篇关于将存储过程的输出分配给第二个SP内的另一个SP的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！