CR And SQL7 Stored Procedures

[Nuffsaid]

Hi All,

I need a little direction on stored procedures. I know how powerful they can be, but I have no idea on how to write the code. A colleague is more familiar with SPs but we’re still having difficulties. Here is what I’m trying to accomplish.

I have info in 3 tables which I cannot link (subreports are not an option). I need the sp to prompt for a date range then return the results to CR. The following is an example of the data I require;

TABLE1:trxdet
FIELDS:date
prod_id
$_amount
account

TABLE2: purchaseorders
FIELDS: date
prod_id
quantity
unitcost

TABLE3: receipts
FIELDS: date
prod_id
quantity
unitcost

We tried hooking CR to an existing SP just to check it out, but that didn’t fly. Kept getting an error stating something like “No Fields Returned“. I’m reporting off of SQL 7 database through ODBC.

If someone would be so kind as to post some SP code that would get us started. We’d really appreciate it !!!!

Thx

Nuffsaid.

 

[AWithers]

I think what you need is a union query

ie
--set up parameters to prompt for start and end date

@startdate as datetime
@enddate as date time

select x,date from table1
where date between @startdate and @enddate

union

select y,date from table2
where date between @startdate and @enddate

union

select z,date from table3
where date between @startdate and @enddate


Hope this helps

Andy

 

[Nuffsaid]

Hey Andy,

Thanks for the tip, we'll give it a shot !!

Keep ya posted.....

Nuffsaid