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.