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_udfProviderChangeAddressTable, Line 33
Incorrect syntax near the keyword 'RETURN'.
Let me know if you need any more info.
Thank you!
Zach
1 4349
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: rdh |
last post by:
Hi all,
I am in process of developing a Server in C++ supporting multiple
protocols. The server will be exposing various functionalities, and the
clients can communicate over any of the...
|
by: Zorba.GR |
last post by:
IBM DB2 Connect Enterprise Edition v8.2, other IBM DB2 (32 bit, 64
bit) (MULTiOS, Windows, Linux, Solaris), IBM iSoft Commerce Suite
Server Enterprise v3.2.01, IBM Tivoli Storage Resource Manager...
|
by: Zeng |
last post by:
I just realized that there are 2 modes for garbage collection modes: server
gs and workstation gs. Would someone know how I can go about changing the
mode for my web application written in C#? I...
|
by: Danieltbt05 |
last post by:
just installed SQL server 2000 and using my client , i can't locate
the server. I used SQL query analyzer to search but no servers were
found. Error message is as below
Server : Msg17,level...
|
by: NiponW |
last post by:
Hi,
I have SQL SERVER 2000 SP4 Enterprise , Windows 2003
Enterprise on
Xeon 4 Processors (now with multi-threading CPU) and I have
questions which
seem weirds to me (used to have the same...
|
by: dorpnospam |
last post by:
We have an old but very critical application that was written in VB 6
against Access 95 dbs. We need to ditch this decrepit old unstable db
platform but we are trying to determine the best...
|
by: TC |
last post by:
Like a lot of database developers, I often choose Jet for the back-
end. I'm starting to worry about what will happen when Jet is
deprecated. Ostensibly, Jet users like me must switch to SQL Server...
|
by: uninvitedm |
last post by:
I'm making a JSP page where (ideally) you select files to upload, each selected file is added to a listbox. The form is submit, (along with some other parameters), and the files are uploaded. They...
|
by: praveenkumarvpk |
last post by:
Hi friends Please help me!
Following is my servlet-code
import java.util.Enumeration;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import...
|
by: Guillermo_Lopez |
last post by:
Hello,
Our company has developed several Access applications for our clients
and we wish to expand to use a database server. We wish to use Access
as the front end application and SQL Server...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |