SQL 从存储过程中的 SELECT 语句中获取标量值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6641113/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get scalar value from SELECT statement in stored proc, from within a stored proc
提问by Jerad Rose
I know the preferred method for returning scalar values from stored procs is either using RETURNor an OUTPUTparameter. But lets say that I have a stored proc that returns the value using a select statement:
我知道从存储过程返回标量值的首选方法是使用RETURN或OUTPUT参数。但是可以说我有一个使用 select 语句返回值的存储过程：
CREATE PROC spReturnNumber AS
SELECT 1
Is it possible to get this value from within another stored proc?
是否可以从另一个存储过程中获取此值？
CREATE PROC spCheckNumber AS
EXEC spReturnNumber -- <-- get the return value here?
Clarification:I need a solution that doesn't require using an OUTPUTparameter, or using RETURNto return the value.
说明：我需要一个不需要使用OUTPUT参数或RETURN用于返回值的解决方案。
Thanks in advance.
提前致谢。
回答by Andomar
You could use insert-execto store the result of a stored procedure in a table:
您可以使用insert-exec将存储过程的结果存储在表中：
declare @t table (col1 int)
insert @t exec spReturnNumber
return (select col1 from @t)
The definition of the table has to match the result set of the stored procedure.
表的定义必须与存储过程的结果集相匹配。
回答by Arnaud F.
You can't get the SELECT value from "parent" procedure but you can get the return value like this:
您无法从“父”过程中获取 SELECT 值，但可以像这样获取返回值：
CREATE PROC A AS
BEGIN
DECLARE @ret int
EXEC @ret = spReturnNumber
RETURN @ret
END
回答by Aaron Bertrand
Use an OUTPUTparameter instead of (or in addition to, if this procedure is used by other applications) the SELECT.
使用OUTPUT参数代替（或除此之外，如果其他应用程序使用此过程）SELECT.
ALTER PROCEDURE dbo.spReturnNumber
@Number INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @Number = 1;
SELECT @Number;
END
GO
CREATE PROCEDURE dbo.spCheckNumber
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Number INT;
EXEC dbo.spReturnNumber @Number = @Number;
SELECT @Number;
END
GO
If you can't change the original procedure, but you know its output will remain static, you could use a #temp table.
如果您无法更改原始过程，但您知道其输出将保持静态，则可以使用 #temp 表。
CREATE PROCEDURE dbo.spCheckNumber
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #n(i INT);
INSERT #n(i) EXEC dbo.spReturnNumber;
DECLARE @Number INT;
SELECT @Number = i FROM #n;
END
GO
回答by Flimzy
Per OP's request, the PostgreSQL way to do this. The first function, foo()just returns an INT. The following functions, bar()and baz()call foo(), in straight SQL, as well as in PL/pgSQL, respectively.
根据 OP 的要求，采用 PostgreSQL 方法来执行此操作。第一个函数foo()只返回一个 INT。以下函数bar()和baz() 分别在直接 SQL 和 PL/pgSQL 中调用foo()。
CREATE FUNCTION foo() RETURNS INT AS $$
SELECT 1
$$ LANGUAGE sql;
CREATE FUNCTION bar() RETURNS INT AS $$
SELECT foo()
$$ LANGUAGE sql;
CREATE FUNCTION baz() RETURNS INT AS $$
DECLARE
x INT;
BEGIN
SELECT INTO x foo();
RETURN x;
END
$$ LANGUAGE plpgsql;
db=# SELECT foo();
foo
-----
1
(1 row)
db=# SELECT bar();
bar
-----
1
(1 row)
db=# select baz();
baz
-----
1
(1 row)
回答by brian chandley
If you are unable to change the proc being called .. place the result set in a temp table [or table variable]:
如果您无法更改被调用的 proc .. 将结果集放在临时表 [或表变量] 中：
CREATE TABLE #results (val INT)
DECLARE @someval int
INSERT #results
EXEC dbo.spCheckNumber
SELECT @someval =val from #results
