You're trying to combine the function of EXEC where it executes an ad hoc sql string with the function of exec where it captures the return status of the execution of a query. They don't go together. Further, you're trying to capture the output of the procedure into a string. That won't work either. Instead, you need to make this a table valued function and simply execute the ad hoc sql string.

----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

i'm not sure what you really want, but my random guess is that you are trying to execute dynamic sql, and you wanted the results to be returned as a database table.

Obviously the function you have created will only return you a string as you have specified "RETURNS Varchar(8000)". If you want the function to return a database table then you'll have to use table value function as suggested by Grant Fritchey. But you'll need to define the table you want to return.

why not just use a stored procedure?It can execute dynamic sql + returns results as a result set

Assuming that, the column JoinDateQuery will only have two values i.e. "(select hiredate from employeeInfo)" or "(select getdate)" then you can define a bit column HasHireDate instead of JoinDateQuery, with this you can define a simple static query like this....