How to call a stored procedure and return a value?

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

  • Any disadvantages to bit flags in database columns?
  • Recursive function in sql server 2005?
  • Are there generic options for version control within a database?
  • Deadlock using linq to sql select
  • Can we create scalar valued function in SQL Server as deterministic and precise?
  • How to get a value from stored procedure
  • 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

  • Regular Expressions in SQL Server servers?
  • Automate SQL Script with Multiple Statement Types
  • How to Delete all data from a table which contain self referencing foreign key
  • What is the SQL Server equivalent to a linked table in MS Access?
  • Disable trigger hangs?
  • Insert Concurrency Issue - Multithreaded Environment
  • 4 Solutions collect form web for “How to call a stored procedure and return a value?”

    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.

    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.

    Try this:

    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 SQL code

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.