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