By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,117 Members | 2,142 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,117 IT Pros & Developers. It's quick & easy.

Return result of dynamic query from function

P: 4
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.
Oct 22 '07 #1
Share this Question
Share on Google+
1 Reply


P: 2
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
Jul 24 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.