如何调用存储过程并返回值？

Hey all, I have a stored procedure and I need to call it within another stored procedure, but I want the first one to return a value (field value).

嘿所有,我有一个存储过程,我需要在另一个存储过程中调用它,但我希望第一个返回一个值(字段值)。

CREATE PROCEDURE rnd_STR
(
    @Length int

)

@alphaVar varchar(10) OUTPUT
AS
SET @alphaVar = 'blah'

 #procedure body
END
GO

DECLARE @alphaVar varchar(10)

EXEC rnd_STR @alphaVar output

SELECT @alphaVar

ERRORS

Msg 102, Level 15, State 1, Procedure rnd_STR, Line 6

消息102,级别15,状态1,过程rnd_STR,第6行

Incorrect syntax near '@alphaVar'.

'@alphaVar'附近的语法不正确。

Msg 137, Level 15, State 1, Procedure rnd_STR, Line 8

消息137,级别15,状态1,过程rnd_STR,第8行

Must declare the scalar variable "@alphaVar".

必须声明标量变量“@alphaVar”。

Msg 2812, Level 16, State 62, Line 4

Msg 2812,Level 16,State 62,Line 4

Could not find stored procedure 'rnd_STR'.

找不到存储过程'rnd_STR'。

(1 row(s) affected)

(1排受影响)

didn't work !!

没用!!

How can I call it??

我怎么称呼它?

BTW, the returned @ID is a string

顺便说一下,返回的@ID是一个字符串

4 个解决方案

#1

You say @alphaVar is varchar(10). In that case you need to use an output parameter as below. Return can only be used for integer types in stored procedures.

你说@alphaVar是varchar(10)。在这种情况下,您需要使用如下的输出参数。返回只能用于存储过程中的整数类型。

CREATE PROCEDURE rnd_STR
@Length int,
@alphaVar varchar(10) OUTPUT    
AS
BEGIN
SET @alphaVar = 'blah'
/* Rest of procedure body*/
END

GO

DECLARE @alphaVar varchar(10) 

EXEC rnd_STR 10, @alphaVar output

SELECT @alphaVar

Alternatively you could use a scalar UDF rather than a stored procedure.

或者,您可以使用标量UDF而不是存储过程。

#2

You're calling syntax is wrong.

你在调用语法是错误的。

 DECLARE @newId int
 EXEC @newId = rnd_STR, @length = 10

See EXECUTE in the reference.

请参阅参考中的EXECUTE。

#3

Try this:

EXEC @alphaVar = rnd_STR 10

#4

A work around of getting this code is to execute the stored procedure in your Management Studio itself and copy the SQL code

获取此代码的一种方法是在Management Studio中执行存储过程并复制SQL代码

#1