Return result of dynamic query from function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Return result of dynamic query from function

Author

Topic

Chinmayee
Starting Member

2 Posts

Posted - 2007-10-22 : 08:54:14

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.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-22 : 09:08:09

You can't do this from function. You need to write a stored proc which will return the resultset.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-22 : 09:16:38

[code]CREATE PROCEDURE dbo.uspPimpMyEmpID
(
@EmpID NVARCHAR(500)
)
AS

SET NOCOUNT ON

DECLARE @SQL NVARCHAR(4000)

SELECT @SQL = JoinDateQuery
FROM NewHireEmployee
WHERE EmpID = @EmpID

EXEC sp_executesql @SQL[/code]

E 12°55'05.25"
N 56°04'39.16"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-23 : 17:57:33

1st, the error was a syntax error due to the

SET @SQLQuery = (select JoinDateQuery from NewHireEmployee where empid= + @empID)
@RESULT = Execute @SQLQuery

lines.

Which should beL
SET @SQLQuery = 'Select JoinDateQuery From NewHireEmployee where empid = ' + Cast(@empID as varchar) + '
Exec (@SQLQuery)

You want the result of the JoinDateQuery correct? As stated above, can't do that in a function, it has to be a stored procedure...

You could use an UPDATE to store the desired result into a table

Create proc UpdateUsingQuery (@empId as varchar(20))
as

Update {TableName}
Set {FeildName} = (select JoinDateQuery from NewHireEmployee where empid=@empID)

What exactly are you intending to do with the results of the JoinDateQuery? Do these need to be added to another table as new records? or do you mean to update existing records in a table?

Chinmayee
Starting Member

2 Posts

Posted - 2007-10-24 : 09:13:26

Thanks a lot..

It works in my application, just what I need to do, is to create field in my table to store the value.

Once again thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources