openquery parameters in a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

openquery parameters in a stored procedure

Author

Topic

leodone
Starting Member

30 Posts

Posted - 2009-07-28 : 17:42:53

I am trying to create a stored procedure that has two parameters. Within this stored procedure I am using Openquery (Openquery is connecting to an Oracel Database).

Everytime I try to execute my stored procedure I get this error message:
Msg 170, Level 15, State 1, Procedure Cash_Report_Allocation, Line 106
Line 106: Incorrect syntax near '+'.

I have tried already to add more ' to the surround parameters within the query but that didn't work.

Can someone help me out on this.

my stored procedure:
USE [OasisReports]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE Cash_Report_Allocation (@Batch_date varchar(10),
@company varchar(1))

AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select OASISPRD.Check_no,
OASISPRD.deposit_date,
OASISPRD.Check_amount,
OASISPRD.Allocated_amount,
OASISPRD.payor,
OASISPRD.receipt_date,
OASISPRD.accounting_date,
OASISPRD.payment_type_code,
OASISPRD.Payment_type_desc,
OASISPRD.batch_date,
OASISPRD.batch_no,
OASISPRD.account_no,
OASISPRD.Last_Invoice_number,
OASISPRD.Last_Invoice_Amount,
OASISPRD.Last_Invoice_Date,
OASISPRD.Last_Invoice_Total,
OASISPRD.Difference_App_Inv_Amount,
OMSTOCK.policy,
OMSTOCK.invoicenumber,
OMSTOCK.amount
from openquery(OASISPRD, 'SELECT receipt_no Check_no,
deposit_date,
receipt_amount Check_amount,
amount Allocated_amount,
payor,
receipt_date,
accounting_date,
payment_type_code,
short_description Payment_type_desc,
batch_date,
batch_no,
account_no,
invoice_no Last_Invoice_number,
minimum_amount_due Last_Invoice_Amount,
invoice_date Last_Invoice_Date,
outstanding_amount Last_Invoice_Total,
amount - minimum_amount_due Difference_App_Inv_Amount
FROM (Select receipt_pk,
decode(payment_type_code,
''CREDITCARD'',
''Credit Card'',
receipt_no) receipt_no,
deposit_date,
receipt_amount,
abs(sum(amount)) amount,
payor,
receipt_date,
min(m.accounting_date) accounting_date,
payment_type_code,
''Payment'' ||
decode(l2.short_description,
NULL,
'' '',
'' - '' || l2.short_description || '' '') ||
decode(batch_type,
''DIRECT_DEB'',
'' '',
''- '' || l.short_description) short_description,
batch_date,
batch_no,
ba.account_no,
fieh.invoice_no,
fieh.minimum_amount_due,
fieh.invoice_date,
os.outstanding_amount

from lookup_code l2,
batch,
lookup_code l,
receipt,
fm_master m,
billing_account ba,
fm_invoice_extract_header fieh,
(Select sum(DECODE(SIGN(sysdate -
(pth.effective_from_date - 30)),
-1,
0,
0,
amount,
1,
amount)) outstanding_amount,
fm.billing_account_fk billing_account_pk
from fm_trans_type ft,
fm_master fm,
policy_term_history pth,
policy p
where ft.code = fm.fm_trans_type_code
and pth.policy_fk = p.policy_pk

and fm.policy_term_history_fk = pth.policy_term_history_pk
AND p.policy_cycle_code = ''POLICY''
AND pth.record_mode_code = ''OFFICIAL''
AND pth.Issue_Company_Entity_Fk = ''' + @company + '''
and fm.accounting_date <=
to_date(''' + @Batch_date + ''', ''yyyy-mm-dd'')
group by fm.billing_account_fk) OS
where l2.lookup_type_code(+) = ''BATCH_TYPE''
and l2.code(+) = nvl(batch_type, ''NORMAL'')
and batch_pk = batch_fk
and l.code = payment_type_code
and l.lookup_type_code = ''PAYMENT_TYPE_CODE''
and receipt_type_code = ''PAYMENT''
and receipt_pk = source_record_fk
and source_table_name = ''RECEIPT''
and m.FM_TRANS_TYPE_CODE != ''TRANSFER''
and ba.issue_company_entity_fk = ''' + @company + '''
and m.billing_account_fk = ba.billing_account_pk
and m.billing_account_fk = os.billing_account_pk(+)

and batch_date = to_date(''' + @Batch_date + ''', ''yyyy-mm-dd'')
and ba.account_no = fieh.account_no
and fieh.invoice_no is not null
and (fieh.account_no, fieh.invoice_date) in
(select x.account_no, max(x.invoice_date)
from fm_invoice_extract_header x
where x.invoice_date <= to_date(''' + @Batch_date + ''', ''yyyy-mm-dd'')

and x.invoice_no is not null
and x.account_no = ba.account_no
group by x.account_no)

group by receipt_pk,
receipt_no,
deposit_date,
receipt_amount,
payor,
receipt_date,
payment_type_code,
''Payment'' ||
decode(l2.short_description,
NULL,
'' '',
'' - '' || l2.short_description || '' '') ||
decode(batch_type,
''DIRECT_DEB'',
'' '',
''- '' || l.short_description),
batch_date,
batch_no,
ba.account_no,
fieh.invoice_no,
fieh.minimum_amount_due,
fieh.invoice_date,
os.outstanding_amount
having sum(amount) <> 0)
order by accounting_date desc') OASISPRD LEFT OUTER JOIN
openquery(OMSTOCK, 'select tblInvoices.policy, tblInvoices.invoicenumber, tblInvoices.amount
from tblInvoices, (Select policy, max(invoicenumber) as Invoice_no
from tblInvoices
where policy in (Select policy
from tblCommon
where currentstockrequirement > 0)
group by policy) xx
where tblInvoices.invoicenumber = xx.Invoice_no ') OMSTOCK
on OASISPRD.account_no = OMSTOCK.policy;
END
GO
--exec Cash_Report_Allocation;

Thanks,

Leo

leodone
Starting Member

30 Posts

Posted - 2009-07-29 : 15:20:06

I was able to figure out how to compile the stored procedure:

My fix was to add a delcare value called: Delcare @varOracleText varchar(8000) and then set @varOracleText = "the select statment" and then adde an execution statement at the end of the stored produre.

Here is my newely modified stored procedure:
ALTER PROCEDURE [dbo].[Cash_Report_Allocation] @Batch_date varchar(10),@company varchar(1)

AS

DECLARE @varOracleText varchar(8000)

--BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Set @varOracleText = '
select OASISPRD.Check_no,
OASISPRD.deposit_date,
OASISPRD.Check_amount,
OASISPRD.Allocated_amount,
OASISPRD.payor,
OASISPRD.receipt_date,
OASISPRD.accounting_date,
OASISPRD.payment_type_code,
OASISPRD.Payment_type_desc,
OASISPRD.batch_date,
OASISPRD.batch_no,
OASISPRD.account_no,
OASISPRD.Last_Invoice_number,
OASISPRD.Last_Invoice_Amount,
OASISPRD.Last_Invoice_Date,
OASISPRD.Last_Invoice_Total,
OASISPRD.Difference_App_Inv_Amount,
OMSTOCK.policy,
OMSTOCK.invoicenumber,
OMSTOCK.amount
from openquery(OASISPRD, ''SELECT receipt_no Check_no,
deposit_date,
receipt_amount Check_amount,
amount Allocated_amount,
payor,
receipt_date,
accounting_date,
payment_type_code,
short_description Payment_type_desc,
batch_date,
batch_no,
account_no,
invoice_no Last_Invoice_number,
minimum_amount_due Last_Invoice_Amount,
invoice_date Last_Invoice_Date,
outstanding_amount Last_Invoice_Total,
amount - minimum_amount_due Difference_App_Inv_Amount
FROM (Select receipt_pk,
decode(payment_type_code,
''''CREDITCARD'''',
''''Credit Card'''',
receipt_no) receipt_no,
deposit_date,
receipt_amount,
abs(sum(amount)) amount,
payor,
receipt_date,
min(m.accounting_date) accounting_date,
payment_type_code,
''''Payment'''' ||
decode(l2.short_description,
NULL,
'''' '''',
'''' - '''' || l2.short_description || '''' '''') ||
decode(batch_type,
''''DIRECT_DEB'''',
'''' '''',
''''- '''' || l.short_description) short_description,
batch_date,
batch_no,
ba.account_no,
fieh.invoice_no,
fieh.minimum_amount_due,
fieh.invoice_date,
os.outstanding_amount

from lookup_code l2,
batch,
lookup_code l,
receipt,
fm_master m,
billing_account ba,
fm_invoice_extract_header fieh,
(Select sum(DECODE(SIGN(sysdate -
(pth.effective_from_date - 30)),
-1,
0,
0,
amount,
1,
amount)) outstanding_amount,
fm.billing_account_fk billing_account_pk
from fm_trans_type ft,
fm_master fm,
policy_term_history pth,
policy p
where ft.code = fm.fm_trans_type_code
and pth.policy_fk = p.policy_pk

and fm.policy_term_history_fk = pth.policy_term_history_pk
AND p.policy_cycle_code = ''''POLICY''''
AND pth.record_mode_code = ''''OFFICIAL''''
AND pth.Issue_Company_Entity_Fk = ''''' + @company + '''''
and fm.accounting_date <=
to_date(''''' + @Batch_date + ''''', ''''mm/dd/yyyy'''')
group by fm.billing_account_fk) OS
where l2.lookup_type_code(+) = ''''BATCH_TYPE''''
and l2.code(+) = nvl(batch_type, ''''NORMAL'''')
and batch_pk = batch_fk
and l.code = payment_type_code
and l.lookup_type_code = ''''PAYMENT_TYPE_CODE''''
and receipt_type_code = ''''PAYMENT''''
and receipt_pk = source_record_fk
and source_table_name = ''''RECEIPT''''
and m.FM_TRANS_TYPE_CODE != ''''TRANSFER''''
and ba.issue_company_entity_fk = ''''' + @company + '''''
and m.billing_account_fk = ba.billing_account_pk
and m.billing_account_fk = os.billing_account_pk(+)

and batch_date = to_date(''''' + @Batch_date + ''''', ''''mm/dd/yyyy'''')
and ba.account_no = fieh.account_no
and fieh.invoice_no is not null
and (fieh.account_no, fieh.invoice_date) in
(select x.account_no, max(x.invoice_date)
from fm_invoice_extract_header x
where x.invoice_date <= to_date(''''' + @Batch_date + ''''', ''''mm/dd/yyyy'''')

and x.invoice_no is not null
and x.account_no = ba.account_no
group by x.account_no)

group by receipt_pk,
receipt_no,
deposit_date,
receipt_amount,
payor,
receipt_date,
payment_type_code,
''''Payment'''' ||
decode(l2.short_description,
NULL,
'''' '''',
'''' - '''' || l2.short_description || '''' '''') ||
decode(batch_type,
''''DIRECT_DEB'''',
'''' '''',
''''- '''' || l.short_description),
batch_date,
batch_no,
ba.account_no,
fieh.invoice_no,
fieh.minimum_amount_due,
fieh.invoice_date,
os.outstanding_amount
having sum(amount) <> 0)
order by accounting_date desc'') OASISPRD LEFT OUTER JOIN
openquery(OMSTOCK, ''select tblInvoices.policy, tblInvoices.invoicenumber, tblInvoices.amount
from tblInvoices, (Select policy, max(invoicenumber) as Invoice_no
from tblInvoices
where policy in (Select policy
from tblCommon
where currentstockrequirement > 0)
group by policy) xx
where tblInvoices.invoicenumber = xx.Invoice_no '') OMSTOCK
on OASISPRD.account_no = OMSTOCK.policy';

BEGIN
EXEC(
'Select * from ' + @varOracleText + ''
)
END

Now my new problem is trying to Execute the stored procedure.

I get this error when I run the problem below:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.

(1 row(s) affected)

USE [OasisReports]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[Cash_Report_Allocation]
@Batch_date = N'5/1/2009',
@company = N'1'

SELECT 'Return Value' = @return_value

GO

Can anyone tell me why it will not exec?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-07-29 : 18:59:33

It's either a genuine syntax error or your SQL string exceeds the 8000 character limit. Look closely at the code within the OPENQUERY section:

openquery(OMSTOCK, ''select tblInvoices.policy, tblInvoices.invoicenumber, tblInvoices.amount
from tblInvoices, (Select policy, max(invoicenumber) as Invoice_no 
from tblInvoices
where policy in (Select policy 
from tblCommon
where currentstockrequirement > 0)
group by policy) xx

I have a feeling one or both of those subqueries is the culprit.

I'd also recommend rewriting those subqueries as JOINS or use the EXISTS statement instead of IN. You should also avoid doing JOINs on an OPENQUERY source, especially if either side could return more than a few thousand rows. Linked server joins spawn remote cursors and kill performance.

leodone
Starting Member

30 Posts

Posted - 2009-08-03 : 12:41:46

Thank you for you info robvolk.
I found out that it was not a syntax error but my last statement was incorrect.

I had this statment:
BEGIN
EXEC(
'Select * from ' + @varOracleText + ''
)
END

I changed it too:

BEGIN
EXEC (@varOracleText)

END

Now it works correctly.

thank you for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources