Take a look at my How to return a value from dynamicaly executed T-SQL code FAQ #8130.

>How can I get a return value returned from dynamic sql? I want to pass a field and a table name and return the max value of the field.
>

>ALTER   FUNCTION fn_MaxId
>   (@pkfield varchar(30) = 'iactionid',
>    @tablename varchar(30) = 'ACTIONLOG'
>    )
>RETURNS bigint
>AS
>BEGIN
>	DECLARE @Res bigint
>	DECLARE @sqlstring varchar(1000)
>	SET @Res = 1
>	set @sqlstring = N'select max('+@pkfield+N') as nextid from '+@tablename
>	exec @sqlstring
>        -- How can I get the max value into the @Res return value???
>	RETURN ( @Res )
>END
>