How to assign an exec result to a sql variable?

declare @EventId int

CREATE TABLE #EventId (EventId int)

insert into #EventId exec rptInputEventId

set @EventId = (select * from #EventId)

drop table #EventId 

I always use the return value to pass back error status. If you need to pass back one value I'd use an output parameter.

sample stored procedure, with an OUTPUT parameter:

CREATE PROCEDURE YourStoredProcedure 
(
    @Param1    int
   ,@Param2    varchar(5)
   ,@Param3    datetime OUTPUT
)
AS
IF ISNULL(@Param1,0)>5
BEGIN
    SET @Param3=GETDATE()
END
ELSE
BEGIN
    SET @Param3='1/1/2010'
END
RETURN 0
GO

call to the stored procedure, with an OUTPUT parameter:

DECLARE @OutputParameter  datetime
       ,@ReturnValue      int

EXEC @ReturnValue=YourStoredProcedure 1,null, @OutputParameter OUTPUT
PRINT @ReturnValue
PRINT CONVERT(char(23),@OutputParameter ,121)

OUTPUT:

0
2010-01-01 00:00:00.000

This will work if you wish to simply return an integer:

DECLARE @ResultForPos INT 
EXEC @ResultForPos = storedprocedureName 'InputParameter'
SELECT @ResultForPos