Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Return result of dynamic query from function Expand / Collapse
Author
Message
Posted Monday, October 22, 2007 4:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, October 22, 2007 5:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:01 PM
Points: 2,717, Visits: 3,856
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
Posted Monday, October 22, 2007 6:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

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

still it gives error

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
Posted Monday, October 22, 2007 6:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:01 PM
Points: 2,717, Visits: 3,856
Sorry, you got me on this one.

______________________________________________________________________

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 #413383
Posted Monday, October 22, 2007 6:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 14,000, Visits: 28,381
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 Query Performance Tuning
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
Posted Tuesday, October 23, 2007 2:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 40,385, Visits: 36,829
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
Posted Tuesday, October 23, 2007 1:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:39 PM
Points: 7,152, Visits: 15,634
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
Posted Wednesday, October 24, 2007 3:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, October 25, 2007 4:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 3:30 AM
Points: 37, Visits: 343
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
Posted Thursday, October 25, 2007 4:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:52 AM
Points: 2,551, Visits: 2,594
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 »

Add to briefcase 12»»

Permissions Expand / Collapse