Return result of dynamic query from function

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Return result of dynamic query from function

11 posts, Page 1 of 2

1 2 »»

Return result of dynamic query from function

Rate Topic

Display Mode

Topic Options

Author

Message

chinmayee

Posted Monday, October 22, 2007 4:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 23, 2007 3:12 AM
Points: 2, Visits: 6

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

Any ideas?

Thanks in advanced!

Post #413342

Jason Selburg

Posted Monday, October 22, 2007 5:34 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:55 AM
Points: 2,582, Visits: 3,552

You need to encapsulate the SQL text in single quotes as below.

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

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Post #413366

chinmayee

Posted Monday, October 22, 2007 6:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, October 23, 2007 3:12 AM
Points: 2, Visits: 6

Hi,

still it gives error Sad

Incorrect syntax near '@RESULT'.

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.

Post #413381

Jason Selburg

Posted Monday, October 22, 2007 6:08 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:55 AM
Points: 2,582, Visits: 3,552

Sorry, you got me on this one. Unsure

Post #413383

Grant Fritchey

Posted Monday, October 22, 2007 6:59 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:57 PM
Points: 13,371, Visits: 25,152

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.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #413412

GilaMonster

Posted Tuesday, October 23, 2007 2:00 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:49 PM
Points: 37,671, Visits: 29,925

Look at sp_executesql

DECLARE @ReturnValue DATETIME, @EmployeeID INT

SET @EmployeeID = 1

exec sp_executesql N'SELECT @JoinDate = JoinDateQuery NewHireEmployee where empid = @EmpID ',
N'@EmpID int, @JoinDate DATETIME OUTPUT', @EmpID = @EmployeeID, @JoinDate = @ReturnValue OUTPUT

SELECT @ReturnValue

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #413728

Matt Miller (#4)

Posted Tuesday, October 23, 2007 1:07 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:49 PM
Points: 6,998, Visits: 13,946

Generically - you cannot use EXECUTE or sp_executeSQL within a function. You can only call functions or extended stored procs from within a function, and those ain't it.

Good news is - you don't need either for what you're doing. You don't need dynamic SQL at all for that matter.

Try this instead:

CREATE FUNCTION dbo.GetJoinDate(@empID as nvarchar(500))
RETURNS Varchar(8000)
AS
BEGIN
DECLARE @RESULT AS NVARCHAR(500)

Select @result=JoinDateQuery from NewHireEmployee where empid= @empID 

return @RESULT
END

----------------------------------------------------------------------------------
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?

Post #414064

rose_red1947

Posted Wednesday, October 24, 2007 3:42 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 19, 2008 1:19 AM
Points: 5, Visits: 7

Try with SET clause before @RESULT = Execute @SQLQuery

Post #414663

jon-474332

Posted Thursday, October 25, 2007 4:24 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, February 18, 2013 1:09 AM
Points: 37, Visits: 334

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 :)

Post #414863

Ramesh Saive

Posted Thursday, October 25, 2007 4:47 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, February 22, 2013 12:03 AM
Points: 2,555, Visits: 2,587

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....

SELECT H.EmpID, COALESCE( E.HireDate, GETDATE() ) FROM NewHireEmployee H LEFT JOIN EmployeeInfo E ON H.EmpID = E.EmpID AND H.HasHireDate = 1
WHERE H.EmpID = @iEmpID

--Ramesh

Post #414872

« Prev Topic | Next Topic »

11 posts, Page 1 of 2

1 2 »»

Permissions