Can This be Done in SQL Server 2000?

[CrystalVis]

Can you please tell me if the following scenario can be done in SQL Server 2000. Your help is greatly appreciated.

This is done in Oracle. I create a package called REPORT_PKG. Within the package, I declare an object called REPORT_TYP as a ref cursor. Please see code below:

CREATE OR REPLACE package REPORT_PKG
as
type REPORT_TYP is ref cursor;
end REPORT_PKG ;
/

Then I create a stored procedure that references the package REPORT_PKG. Please see below

CREATE OR REPLACE procedure OPEN_ORDER_PROC
(
OPEN_ORDER_CUR in out REPORT_PKG.REPORT_TYP
)
as

begin

open OPEN_ORDER_CUR for

SELECT
NVL(pay.type,' ') as Type,
NVL(pay.amount,0) as Amount,
NVL(ordD.status,' ') as Status,
NVL(pay.paynumber,0) as PayNumber

FROM
contact con,
order ord,
orderdetail ordD,
payment pay


WHERE
con.row_id = ord.contact_id
AND ord.row_id = ordD.order_id
AND ordD.row_id = pay.par_row_id
AND con.customer_contact = 'Y'
AND pay.reason = 'Multi'
AND pay.approval IN('Declined','Final Decline','Waiting')

;

end OPEN_ORDER_PROC;
/

 

[topher0303]

No need to explicitly create a cursor on SQL server. Just specify the select statement:

CREATE procedure OPEN_ORDER_PROC
as
SELECT
NVL(pay.type,' ') as Type,
NVL(pay.amount,0) as Amount,
NVL(ordD.status,' ') as Status,
NVL(pay.paynumber,0) as PayNumber
FROM
contact con,
order ord,
orderdetail ordD,
payment pay
WHERE
con.row_id = ord.contact_id
AND ord.row_id = ordD.order_id
AND ordD.row_id = pay.par_row_id
AND con.customer_contact = 'Y'
AND pay.reason = 'Multi'
AND pay.approval IN('Declined','Final Decline','Waiting')

 

[CrystalVis]

topher0303,

Thank you for your quick respond. Will Crystal Report sees the stored procedure if I don't use cursor? I'm trying to create a stored proc and than design report base on it. What about runtime parameters? For example, I want to allow the user to select the data base on order date range and order amount. Will this work?

CREATE procedure OPEN_ORDER_PROC
@StartDate
@EndDate
@Amount

as
SELECT
NVL(pay.type,' ') as Type,
NVL(pay.amount,0) as Amount,
NVL(ordD.status,' ') as Status,
NVL(pay.paynumber,0) as PayNumber
FROM
contact con,
order ord,
orderdetail ordD,
payment pay
WHERE
con.row_id = ord.contact_id
AND ord.row_id = ordD.order_id
AND ordD.row_id = pay.par_row_id
AND con.customer_contact = 'Y'
AND pay.reason = 'Multi'
AND pay.approval IN('Declined','Final Decline','Waiting')
AND ord.OrderDate >= @StartDate
AND ord.OrderDate < @EndDate
AND ord.TotAmt = @Amount

Sorry, I forgot to include this info in the original post. I'm using CR 8.5 and SQL Server 2000. Thanks

 

[CrystalVis]

oops...I forgot to declare the datatype for the parameters. They should have been:

@StartDate date,
@EndDate date,
@Amount number

 

[fluteplr]

Yes it will.

But in my experience it is a lot easier to work with views and pass crystal the where clause needed for to select the correct records from the view.

The biggest problem with procedures and crystal is that crystal need to run the proc to get the schema of the result table. With a view this is instant and automatic.

 

[CrystalVis]

From what I understand is stored procedure is precompiled. Therefore, it will help with performance and it is more powerful than view. Is there a way to declare a date range in the stored procedure instead of using two parameters? I know I can pass in a date range from Crystal to the stored proc.

 

[topher0303]

Views are indeed another way to pull data from Crystal. However, with views you have to be very careful. A complicated view can cause performance problems. If the view has nested selects, it could be more efficient with stored procedures if you need to strategically place a parameter in the nested select. A view often will run the entire statement and filter afterwards.

Also, if you join views, and SQL Server is unable to merge the views, you can also take a performance hit. This causes the views to be run independently and the data joined after the fact. This can cause more records to be selected and joined before being filtered.

Chris.