Dave,

It depends what do you need the return value for. I you need to use it within SELECT statement, you should use user function.

SELECT dbo.MyFunction()

CREATE FUNCTION dbo.MyFunction
RETURNS INT
AS
BEGIN
RETURN (SELECT 1234)
END

or you can use Stored procedure call with OUTPUT parameter

DECLARE @x INT

EXECUTE dbo.MyFunction 
     @out_value = @x OUTPUT

CREATE PROCEDURE dbo.MyProcedure
      @out_value INT OUTPUT
AS
BEGIN
    SELECT @out_value = 1234
END

>Hi,
>
>Is it possible to assign an return value from an Stored Proc to a variable locale.
>
>My first SP return a value.
>I want that my second SP exec that first SP in the second.
>
>Example:
>Declare @x int
>set @x = exec SP1
>select @x
>
>I got an error.
>
>Thank in advance.