Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2003
    Location
    Montreal
    Posts
    38

    Question Unanswered: 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.

  2. #2
    Join Date
    Feb 2005
    Posts
    78
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •