Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 509
  • Last Modified:

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
0
thayduck
Asked:
thayduck
  • 4
  • 3
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
thayduckAuthor 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
 
thayduckAuthor 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
 
thayduckAuthor 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
 
thayduckAuthor 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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now