Return result of dynamic query from function

[CAG]

Hi,

In my application, i'm storing sql query as value of table field in database.

Like in my NewHireEmployee table, value of JoinDateQuery field can be (select hiredate from employeeInfo) or simply (select getdate)

I want to fetch this query, evaluate it and then return the result of query.

I tried to write a function like this,

CREATE FUNCTION dbo.ExecuteStringAsQuery
(@empID as nvarchar(500))
RETURNS Varchar(8000)
AS
BEGIN
DECLARE @SQLQuery AS NVARCHAR(500),
@RESULT AS NVARCHAR(500)
/* Build Transact-SQL String with parameter value */
SET @SQLQuery = (select JoinDateQuery from NewHireEmployee where empid= + @empID)
@RESULT = Execute @SQLQuery
return @RESULT
END

But it is giving error like
'Line 10: Incorrect syntax near '@RESULT'.

I'm using sql server 2000

I want the result of query stored in database table.. I'm succeeded to fetch the qeury. But it is as string.

I'm not sure of how to trigger a string to be executed as sql query & return resultset.

Thanks.

[arbcoll]

Hi pal,
I believe till now you have learned much about SQL, and you already know now that the code provided here is whole wrong.
Here are some tips about it:

1. Functions can return only scalar values, or tables
2. Dynamic Queries cannot be used in functions. Use Stored Procs if you need them.
3. Variable assigning is done via (simpliest) SET command in SQL
4. When using text strings, always put them inside single cuotes (')

Rgds