I have a stored procedure that returns a valueI call it from other stored procedures that need to retrieve this value.
DECLARE @localVariable INT
EXEC @localVariable = GetMyValue
SELECT @localVariable
DECLARE @localVariable INSERT @localVariable = (SELECT TOP 1 newvalue FROM @tmpNewValue)