How to call a stored procedure and return a value?
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
Msg 102, Level 15, State 1, Procedure rnd_STR, Line 6
Incorrect syntax near ‘@alphaVar’.
Msg 137, Level 15, State 1, Procedure rnd_STR, Line 8
Must declare the scalar variable “@alphaVar”.
Msg 2812, Level 16, State 62, Line 4
Could not find stored procedure ‘rnd_STR’.
(1 row(s) affected)
didn’t work !!
How can I call it??
BTW, the returned @ID is a string
4 Solutions collect form web for “How to call a stored procedure and return a value?”
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.
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.
You’re calling syntax is wrong.
DECLARE @newId int EXEC @newId = rnd_STR, @length = 10
See EXECUTE in the reference.
EXEC @alphaVar = rnd_STR 10
a work around of getting this code is to execute the stored procedure in your
Management Studio it self and copy the