How to call a stored procedure and return a value?

Question

Hey, I have a stored procedure and I need to call it in another stored procedure, but I want the first 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

Invalid syntax near '@alphaVar'.

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

Must declare @alphaVar scalar variable.

Msg 2812, Level 16, State 62, Line 4

Could not find stored procedure "rnd_STR".

(1 row (s) affected)

does not work!!

How can I call him?

BTW returned by @ID is a string

Answer 1

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

 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 can use scalar UDF rather than a stored procedure.

Answer 2

You are calling the syntax incorrectly.

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

See EXECUTE in the link.

Answer 3

Try the following:

 EXEC @alphaVar = rnd_STR 10 

Answer 4

Work on this code is to execute a stored procedure in your Management Studio and copy the SQL code