在另一个SP内部调用SP,将值(内部sp的输出)赋值给变量(outter sp的变量) [英] Call SP inside another SP, assign value (output of inner sp) to a variable (variable of the outter sp)
本文介绍了在另一个SP内部调用SP,将值(内部sp的输出)赋值给变量(outter sp的变量)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想使用SP输出将其分配给另一个SP的变量。就像这里一样,我想输出GetLinage SP在InsertLog SP中分配给@linage,但我得到的错误是,
过程 或 function GetLinage指定的参数太多。
我不知道我错了什么,
EXEC InsertLog ' MenuItem78', ' thisrout',' thisbutton',' 2000-01-01 00:00:00' , 123
ALTER PROCEDURE
InsertLog(
@ formName nvarchar ,
@ buttonRout nvarchar ,
@ buttonName nvarchar ,
@ clickDateTime DateTime ,
@ userCode bigint
)
AS
BEGIN
DECLARE
@ linage nvarchar
EXEC GetLinage @ formName , @linage 输出
INSERT INTO
LogTable(
FormName,
Linage,
ButtonRout,
ButtonName,
ClickDateTime,
UserCode
)
VALUES (
@ formName ,
@ linage ,
@ buttonRout ,
@ buttonName ,
@clickDateTime ,
@ userCode
)
结束
GO
GetLinage SP如下:
CREATE PROCEDURE GetLinage( @ formname nvarchar )
AS
SELECT Linage
FROM formslist
WHERE (formslist.Formname = @ formname )
GO
我是什么尝试过:
我正在研究它和其他不同的方法。但是知道答案。
解决方案
你没有从你的第二个返回一个值程序。
尝试将其修改为:
ALTER PROCEDURE GetLinage(
@ formname nvarchar ,
@ linage nvarchar OUTPUT
)
AS
SELECT @ linage = Linage
FROM formslist
WHERE (formslist.Formname = @ formname )
GO
...希望它有所帮助。
检查以下解决方案。因此,您无需更改内部SP中的逻辑。
注意 :此解决方案仅适用如果
1.内部SP不包含以下声明
2.它只会给出1行的值,即使SP返回多行。
插入 进入 #Table
EXEC ....
脚本:
更改 程序 InsertLog
(
@ formName nvarchar ,
@ buttonRout nvarchar ,
@ buttonName nvarchar ,
@ clickDateTime DateTime ,
@ userCode bigint
)
AS
BEGIN
DECLARE < span class =code-sdkkeyword> @ linage nvarchar ( 30 )
创建 表 #Result(linage nvarchar ( 30 ))
插入 进入 #Result
EXEC #GetLinage @ formName - ,@ linage OUTPUT
选择 @ linage = linage 来自 #Result
INSERT INTO
LogTable(
FormName,
Linage,
ButtonRout,
ButtonName,
ClickDateTime,
UserCode
)
选择 @ FormName , @ Linage , @ ButtonRout , @ ButtonName , @ ClickDateTime , @ UserCode
Hi,
I'd like to use an SP output to assign it to a variable of another SP Like here, I want to output of the GetLinage SP be assigned to @linage in InsertLog SP, But an error I get is that,
Procedure or function GetLinage has too many arguments specified.
I Don't know if what I'm mistaking,
EXEC InsertLog 'MenuItem78', 'thisrout', 'thisbutton', '2000-01-01 00:00:00' , 123 ALTER PROCEDURE InsertLog( @formName nvarchar, @buttonRout nvarchar, @buttonName nvarchar, @clickDateTime DateTime, @userCode bigint ) AS BEGIN DECLARE @linage nvarchar EXEC GetLinage @formName, @linage OUTPUT INSERT INTO LogTable( FormName, Linage, ButtonRout, ButtonName, ClickDateTime, UserCode ) VALUES( @formName, @linage, @buttonRout, @buttonName, @clickDateTime, @userCode ) END GO
The GetLinage SP is as follows:
CREATE PROCEDURE GetLinage (@formname nvarchar) AS SELECT Linage FROM formslist WHERE (formslist.Formname = @formname) GO
What I have tried:
I'm working on it and other different ways for it. but yet know answer.解决方案Hi,
You don't return a value from your 2nd procedure.
Try modifying it to something like:
ALTER PROCEDURE GetLinage ( @formname nvarchar, @linage nvarchar OUTPUT ) AS SELECT @linage = Linage FROM formslist WHERE (formslist.Formname = @formname) GO
... hope it helps.
Check below Solution. So you don't have to change logic in inner SP.
Note: This solution will work only if
1. inner SP not contain below statement
2. It will give value of 1 row only, even SP returns multiple rows.
Insert Into #Table EXEC ....
Script:
Alter PROCEDURE InsertLog ( @formName nvarchar, @buttonRout nvarchar, @buttonName nvarchar, @clickDateTime DateTime, @userCode bigint ) AS BEGIN DECLARE @linage nvarchar(30) Create Table #Result (linage nvarchar(30)) Insert Into #Result EXEC #GetLinage @formName--, @linage OUTPUT Select @linage = linage From #Result INSERT INTO LogTable( FormName, Linage, ButtonRout, ButtonName, ClickDateTime, UserCode ) Select @FormName, @Linage, @ButtonRout, @ButtonName, @ClickDateTime, @UserCode
这篇关于在另一个SP内部调用SP,将值(内部sp的输出)赋值给变量(outter sp的变量)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文