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