Hi there,

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

CREATE PROCEDURE [dbo].[spName]

(@empID AS NVARCHAR(500))

AS

BEGIN

SET NOCOUNT ON;

SET @SQL = 'select JoinDateQuery from NewHireEmployee where empid=' + @empID

EXEC(@SQL)

END

RETURN

OR

Where @SQL = 'select JoinDateQuery from NewHireEmployee where empid=001'

or

@SQL = 'select getdate()'

CREATE PROCEDURE [dbo].[spName]

(@SQL AS VARCHAR(8000))

AS

BEGIN

SET NOCOUNT ON;

EXEC(@SQL)

END

RETURN

But be caution about using dynamic sql 😉

cheers 🙂