将函数返回值传递给存储过程参数 [英] Passing function return value to stored procedure parameter

问题描述

有没有办法将函数的返回值直接传递给存储过程参数，而无需先存储在变量中?

Is there a way to pass the return value of a function directly into a stored procedure parameter without first storing in a variable?

例如，给定以下存储过程(将在 databaseA 上)

For example, given the following stored procedure (which will be on databaseA)

CREATE PROCEDURE [dbo].[MySproc]
   @NAME NVARCHAR(50)
AS

我希望能够调用以下内容(来自 databaseB)...

I would like to be able to call the following (from databaseB)...

EXEC [databaseA]..MySproc DB_NAME()

这会导致以下错误:

')' 附近的语法不正确.

Incorrect syntax near ')'.

以下有效，但需要两行...

The following works, but requires two lines...

DECLARE @NAME NVARCHAR(50) = DB_NAME()
EXEC [databaseA]..MySproc @NAME

从表面上看，我意识到这不是什么大问题，但如果我能把它作为一条线，它真的会对我的情况有所帮助.我的假设是这根本不可能

I realise on the surface this is not a big issue, but it would really help my situation if I could have it as a single line. My assumption is this is simply not possible

推荐答案

没有.来自 EXECUTE (Transact-SQL) 存储过程的语法如下:

No. From EXECUTE (Transact-SQL) the syntax for a stored procedure is the following:

Execute a stored procedure or function  
[ { EXEC | EXECUTE } ]  
    {   
      [ @return_status = ]  
      { module_name [ ;number ] | @module_name_var }   
        [ [ @parameter = ] { value   
                           | @variable [ OUTPUT ]   
                           | [ DEFAULT ]   
                           }  
        ]  
      [ ,...n ]  
      [ WITH <execute_option> [ ,...n ] ]  
    }  
[;]  

请注意，对于参数，传递的值必须是(文字)值、变量或 DEFAULT 关键字.它不能是一个表达式，它会包含简单的表达式 @Variable + 'string' 和 标量函数，比如 GETDATE().

Notice that for the parameters the passed value must be a (literal) value, a variable, or the DEFAULT keyword. It cannot be an expression, which would include something like the simple expression @Variable + 'string' and scalar functions like GETDATE().

因此，您必须先将值存储在变量中，然后将该变量传递给过程.

As such you must store the value first in a variable and then pass that variable to the procedure.

这篇关于将函数返回值传递给存储过程参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！