Showing posts with label openquery. Show all posts
Showing posts with label openquery. Show all posts

Monday, March 19, 2012

Function with EXECUTE problem

Hello,

I'm running into some trouble writing a function that returns a table. I'm using OPENQUERY in the FROM clause to fetch data from a remote server (ORACLE). Because the query must be dynamic, I have to execute it using the EXECUTE command.

Basically, I first dynamically create a string containing my query and then I execute it by calling the EXECUTE function and passing my string as an argument.

Now, I'd like my function to return what's produced by the EXECUTE call. Here's how I wrote the function declaration:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

CREATE FUNCTION [dbo].[beh_GetRemoteData] (@.STARTDATE varchar(20), @.POS int, @.ID_ANALOG varchar(10))
RETURNS @.RET TABLE (HIST_TIMESTAMP DATETIME, ID_ANALOG INT, STATUT INT, QUALITY INT, VALUE NUMERIC(12,5)) AS
BEGIN
DECLARE @.REMOTEQUERY varchar(300)
DECLARE @.LOCALQUERY varchar(400)

--======== Create remote Query =======--
SET @.REMOTEQUERY = 'SELECT * FROM [...]'


--======== Create local Query =======--

SET @.LOCALQUERY = 'SELECT [...] FROM OPENQUERY(REMOTE_SERVER, ''' + @.REMOTEQUERY + ''')'

INSERT INTO @.RET
EXEC(@.LOCALQUERY)

RETURN
END

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Of course, this doesn't even passes the "Check syntax" because the EXEC statement cannot be used as as source when inserting into a table variable. This might be but it's EXACTELY what I want to do.

Any ideas on how I should write my function?

Thanks,

Skip.According to my reading of books on line for SQL Server 2000 you can't execute anything other than an extended stored procedure inside a function anyway - and those can't return results sets. Therefore, you can't use dynamic sql either.