SQL server multi-statement table function w/ openquery

[zachster17]

Hello all,

I'm trying to write a multi-statement table function that returns a table of addresses from a remote database (Oracle) using OpenQuery and I'm having a hard time getting it to work with the 1 variable constraint it has (provider ID)

The code is below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE FUNCTION pmf_udfProviderChangeAddressTable
(
    -- Add the parameters for the function here
    @ProviderID varchar(20)
)
RETURNS @AddressTable TABLE
(
    -- Add the column definitions for the TABLE variable here
    ContactType varchar(4),
    ContactAddr1 varchar(100),
    ContactAddr2 varchar(100),
    ContactCity varchar(40),
    ContactState varchar(3),
    ContactZip varchar(10),
    ContactPhone varchar(20),
    ContactFax varchar(20)
)
AS
BEGIN
 
    DECLARE @SQL As varchar(500)
    SET @SQL = 'SELECT CONTACT_TYPE As ContactType, FIRM_NAME As ContactAddr1,
    ADDRESS_LINE1 As ContactAddr2, CITY AS ContactCity, STATE As ContactState, ZIP_CODE As ContactZip,
    PHONE_NBR As ContactPhone, FAX_NBR As ContactFax FROM NET$.CONTACTS
    WHERE PROV_ID = ''' + @ProviderID + ''' AND
    (CONTACT_TYPE = ''01'' OR CONTACT_TYPE = ''02'')'
 
 
    -- Add the SELECT statement with parameter references here
    EXEC('INSERT INTO @AddressTable SELECT * FROM OPENQUERY(ENCP, ' + @SQL + ')'
 
    RETURN 
END
GO
 

I keep getting this error:

Msg 156, Level 15, State 1, Procedure pmf_udfProvider ChangeAddressTa ble, Line 33
Incorrect syntax near the keyword 'RETURN'.


Let me know if you need any more info.

Thank you!

Zach

[ck9663]

I think you're missing one close parenthesis on your EXEC statement. You have ')' to close the OPENQUERY, but missing one to close the EXEC statement.

-- CK