How do you pass SQL stored procedure parm to linked Oracle query within that same stored procedure

I am trying tp use the parm passed to the sql  stored procedure in the WHERE clause in the linked oracle openquery within that sql stored procedure but keep getting errors or no records are witten to the table.
I have called that parm   :param1 in the where clause but get errors. The way the where clause is now runs but gives me no records.     Man, what am I missing, it can't be that hard......

USE [RMReports]
GO
/****** Object:  StoredProcedure [dbo].[usp_rmagegroup]    Script Date: 11/08/2008 17:26:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_rmagegroup]

 @PARAM1 varchar(75) = '3M COMPANY'


      
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

IF OBJECT_ID('[dbo].[tbl_rmagegroup]') IS NOT NULL
    drop TABLE [dbo].[tbl_rmagegroup]

CREATE TABLE [dbo].[tbl_rmagegroup]
(
[company] [varchar](75) NULL,      
[salesmn] [varchar](30) NULL,      
[custno] [varchar](60) NULL,
[trantype] [varchar](1) NULL,
[invdate] [varchar](20) NULL,
[balance] [decimal](11,2) NULL,
[npest] [decimal](5,0) NULL,
[custnocode] [varchar](1) NULL

       
) ON [PRIMARY]
      
INSERT INTO [dbo].[tbl_rmagegroup]

SELECT  * from openquery

 
([GETPAIDE.CN.CA],

'SELECT  GPCOMP1.GPCUST.COMPANY, GPCOMP1.GPCUST.SALESMN, GPCOMP1.GPCUST.CUSTNO, GPCOMP1.GPRECL.TRANTYPE, GPCOMP1.GPRECL.INVDATE, GPCOMP1.GPRECL.BALANCE, GPCOMP1.GPCUST.NPEST,
            SUBSTR(GPCOMP1.GPCUST.CUSTNO, 1, 1) AS CUSTNOCODE
FROM    GPCOMP1.GPCUST, GPCOMP1.GPRECL
WHERE  GPCOMP1.GPCUST.CUSTNO = GPCOMP1.GPRECL.CUSTNO AND (GPCOMP1.GPCUST.COMPANY = '' @PARAM1 '')
ORDER BY GPCOMP1.GPCUST.COMPANY, CUSTNOCODE, GPCOMP1.GPCUST.CUSTNO')

end
thayduckProgrammer AnalystAsked:
Who is Participating?
 
thayduckConnect With a Mentor Programmer AnalystAuthor Commented:
After about 18 hours of testing and searching internet I found this blog that explained how to pass a parm within a openquery that is accessing Oracle tables via linked oracle servers. Or maybe just passing a parm within a openquery to. Anyways, it took me a couple more hours but I finally got it to work.


 http://blogs.conchango.com/jamespipe/archive/2007/06/28/SQL-Server-2005_3A00_-Passing-variables-into-an-OPENQUERY-argument.aspx

My original query (linking to Oracle Tables via openquery that no matter what I did would not retrieve any records because of parm problems in WHERE clause:

--INSERT INTO [dbo].[tbl_rmagegroup]

--SELECT  * from openquery

 
--([GETPAIDE.CN.CA],


--'SELECT  GPCOMP1.GPCUST.COMPANY, GPCOMP1.GPCUST.SALESMN, GPCOMP1.GPCUST.CUSTNO, GPCOMP1.GPRECL.TRANTYPE, GPCOMP1.GPRECL.INVDATE, GPCOMP1.GPRECL.BALANCE, GPCOMP1.GPCUST.NPEST,
 --           SUBSTR(GPCOMP1.GPCUST.CUSTNO, 1, 1) AS CUSTNOCODE
--FROM    GPCOMP1.GPCUST, GPCOMP1.GPRECL
--WHERE  GPCOMP1.GPCUST.CUSTNO = GPCOMP1.GPRECL.CUSTNO AND GPCOMP1.GPCUST.COMPANY = @company
--ORDER BY GPCOMP1.GPCUST.COMPANY, CUSTNOCODE, GPCOMP1.GPCUST.CUSTNO')

 What I did after finding Blog:     AND IT WORKS...        

Mke sure to add                         @MyString    varchar(max)


set     @MyString =  'SELECT  GPCOMP1.GPCUST.COMPANY, GPCOMP1.GPCUST.SALESMN, GPCOMP1.GPCUST.CUSTNO, GPCOMP1.GPRECL.TRANTYPE, GPCOMP1.GPRECL.INVDATE, GPCOMP1.GPRECL.BALANCE, GPCOMP1.GPCUST.NPEST,
            SUBSTR(GPCOMP1.GPCUST.CUSTNO, 1, 1) AS CUSTNOCODE
FROM    GPCOMP1.GPCUST, GPCOMP1.GPRECL                
                   
                     WHERE  GPCOMP1.GPCUST.CUSTNO = GPCOMP1.GPRECL.CUSTNO AND GPCOMP1.GPCUST.COMPANY = (''' + convert(varchar(75),@company,120) + ''')
                     ORDER BY GPCOMP1.GPCUST.COMPANY, CUSTNOCODE, GPCOMP1.GPCUST.CUSTNO'


set     @MyString =  N'INSERT INTO [dbo].[tbl_rmagegroup]

SELECT  * from openquery

 
([GETPAIDE.CN.CA],    ''' + REPLACE(@MyString, '''', '''''') + ''')'


--print   @MyString

 

EXEC    (@MyString)





0
 
tigin44Commented:

take the @param1 variable out of the string like the one below...

'SELECT  GPCOMP1.GPCUST.COMPANY, GPCOMP1.GPCUST.SALESMN, GPCOMP1.GPCUST.CUSTNO, GPCOMP1.GPRECL.TRANTYPE, GPCOMP1.GPRECL.INVDATE, GPCOMP1.GPRECL.BALANCE, GPCOMP1.GPCUST.NPEST,
            SUBSTR(GPCOMP1.GPCUST.CUSTNO, 1, 1) AS CUSTNOCODE
FROM    GPCOMP1.GPCUST, GPCOMP1.GPRECL
WHERE  GPCOMP1.GPCUST.CUSTNO = GPCOMP1.GPRECL.CUSTNO AND (GPCOMP1.GPCUST.COMPANY = ' + @PARAM1 +')
ORDER BY GPCOMP1.GPCUST.COMPANY, CUSTNOCODE, GPCOMP1.GPCUST.CUSTNO')
0
 
tigin44Commented:
sory I missed the quotations... since your parameter is string value it must be like this one

'SELECT  GPCOMP1.GPCUST.COMPANY, GPCOMP1.GPCUST.SALESMN, GPCOMP1.GPCUST.CUSTNO, GPCOMP1.GPRECL.TRANTYPE, GPCOMP1.GPRECL.INVDATE, GPCOMP1.GPRECL.BALANCE, GPCOMP1.GPCUST.NPEST,
            SUBSTR(GPCOMP1.GPCUST.CUSTNO, 1, 1) AS CUSTNOCODE
FROM    GPCOMP1.GPCUST, GPCOMP1.GPRECL
WHERE  GPCOMP1.GPCUST.CUSTNO = GPCOMP1.GPRECL.CUSTNO AND (GPCOMP1.GPCUST.COMPANY = ''' + @PARAM1 +''')
ORDER BY GPCOMP1.GPCUST.COMPANY, CUSTNOCODE, GPCOMP1.GPCUST.CUSTNO')
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Mark GeerlingsDatabase AdministratorCommented:
It looks like you posted this both the SQL Server zone and the Oracle zone on this site and you use SQL Server syntax, but you mention Oracle in your question, so I'm puzzled.  Is this a SQL Server question, or an Oracle question?  The "create table..." syntax is not directly supported in Oracle stored procedures.  

Oracle stored procedure syntax supports only the four basic SQL verbs (select, insert, update and delete) directly.  Other SQL verbs in Oracle procedures are only supported in the "execute immediate" command.
0
 
thayduckProgrammer AnalystAuthor Commented:
tigin44:
Got this message when trying your suggestion
Msg 102, Level 15, State 1, Procedure usp_rmagegroup, Line 42
Incorrect syntax near '+'.

markqeer:

In this sql stored procedure, I am trying to fill a sql work table with data from 2 oracle tables by using a openquery to access those 2 oracle tables. That is why I pointed this question to SQL and Oracle.
The problem I am having is that in the openquery it will not accept the parm that was fed to the sql stored procedure. The sql stored procedure parm starts with a @ but the openquery to oracle doesnt like the @.
0
 
thayduckProgrammer AnalystAuthor Commented:
= '' + @PARAM1 + '' works in the WHERE clause  with no error but returns no records.

Remember, the openquery part of this sql stored procedure is pointing to and reading from a Oracle server.  That is why the @param1 in the WHERE clause of the openquery is not working. Oracle does not like '' + @param1 + ''. Also tried  '' + :param1 + '' and that did not work either.


 
0
 
tigin44Commented:
are you building the sql sytax dynamically and passing it to the oracle... or is this an sql statement in oracle...
0
 
thayduckProgrammer AnalystAuthor Commented:
No.

If I run this SQL Stored procedure without any parms then:

It will buld a work table in sql database RMREPORTS.
It will run a openquery that accesses oracle tables (within this sql stored procedure) and puts that data into my sql work table, thats it.
This works fine without the introduction of a parm.

The problem is that I only want certain records from the oracle tables.
So i changed this sql stored procedure to accept a parm (@param1).
Now, the parm is accepted by the sql stored procedure but I cannot figure out a way to use that parm in a WHERE clause in my openquery part of my sql stored procedure that is accessing the Oracle tables.

I can get the sql stored procedure to run, but it doesn't retrieve any records from the oracle tables.

From what I read, there is a trick to get the the openquery to oracle tables to use that parm in a WHERE clause.

This below code in the sql stored procedure points that openquery to the oracle server via our tnsnames.ora table.

SELECT  * from openquery
 
([GETPAIDE.CN.CA],

 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.