Crystal Reports and temp tables in a stored procedure.

[FrankPhillips]

I have a stored procedure i need to use for a report. however, when i select this stored procedure, no fields show up in the field explorer.

This stored procedure runs fine, and gives me the correct output; I've read that Crystal reports can handle temp tables per their own website. However, for the life of me I cannot access the fields.

Im at a loss since I have some fields that are created on the fly based on data from multiple tables.

Any help would be appreciate any suggestions. If its something with the stored procedure itself, cr is reallllly picky because as i said, this works anyplace else.

Thanks

Frank




This is my stored procedure:

CREATE PROCEDURE sp_PracticeBatch @BatchID int, @PracticeID int AS


CREATE TABLE #AllClaims (
[claim_type] [varchar] (1) ,
[ClaimID] [int],
[prac_id] [int] NOT NULL ,
[prov_id] [int] NOT NULL ,
[loc_id] [int] NOT NULL ,
[region_id] [int] NOT NULL ,
[employer_id] [int] NOT NULL ,
[employee_id] [int] NOT NULL ,
[patient_id] [int] NOT NULL ,
[plan_id] [int] NOT NULL ,
[claim_date] [datetime] NOT NULL ,
[par_claim] [varchar] (1) ,
[non_par_fee] [money] NULL ,
[pre_claim_used] [money] NULL ,
[pre_claim_remain] [money] NULL ,
[employer_liab] [money] NULL ,
[employee_liab] [money] NULL ,
[ddn_fee] [money] NULL ,
[other_ins_payment] [money] NULL ,
[remit_amount] [money] NULL ,
[claim_status] [int] NULL ,
[emp_batch_id] [int] NULL ,
[prac_batch_id] [int] NULL ,
[remit_date] [datetime] NULL
)







if @PracticeID = 0
begin
insert into #AllClaims (
claim_type,
ClaimID,
prac_id,
prov_id,
loc_id,
region_id,
employer_id,
employee_id,
patient_id,
plan_id,
claim_date,
par_claim,
non_par_fee,
pre_claim_used,
pre_claim_remain,
employer_liab,
employee_liab,
ddn_fee,
other_ins_payment,
remit_amount,
claim_status,
emp_batch_id,
prac_batch_id,
remit_date)

(select
'D',
ClaimID,
prac_id,
prov_id,
loc_id,
region_id,
employer_id,
employee_id,
patient_id,
plan_id,
claim_date,
par_claim,
non_par_fee,
pre_claim_used,
pre_claim_remain,
employer_liab,
employee_liab,
ddn_fee,
other_ins_payment,
remit_amount,
claim_status,
emp_batch_id,
prac_batch_id,
remit_date
from claimmaster C
where C.prac_batch_id = @BatchID)


insert into #AllClaims (
claim_type,
ClaimID,
prac_id,
prov_id,
loc_id,
region_id,
employer_id,
employee_id,
patient_id,
plan_id,
claim_date,
par_claim,
non_par_fee,
pre_claim_used,
pre_claim_remain,
employer_liab,
employee_liab,
ddn_fee,
other_ins_payment,
remit_amount,
claim_status,
emp_batch_id,
prac_batch_id,
remit_date)

(select
'O',
ClaimID,
prac_id,
prov_id,
loc_id,
region_id,
employer_id,
employee_id,
patient_id,
plan_id,
claim_date,
par_claim,
non_par_fee,
pre_claim_used,
pre_claim_remain,
employer_liab,
employee_liab,
ddn_fee,
other_ins_payment,
remit_amount,
claim_status,
emp_batch_id,
prac_batch_id,
remit_date
from Orthomaster C
where C.prac_batch_id = @BatchID)



select distinct C.*,D.*, P.* ,Q.*
from #AllClaims C, dependants D , Providers P, Practice_Master Q
where
C.prov_id = P.pro_id and
C.patient_id = D.de_id and
C.prac_id = Q.pr_id and
C.prac_batch_id = @BatchID
order by C.claim_date

end
else
begin

insert into #AllClaims (
claim_type,
ClaimID,
prac_id,
prov_id,
loc_id,
region_id,
employer_id,
employee_id,
patient_id,
plan_id,
claim_date,
par_claim,
non_par_fee,
pre_claim_used,
pre_claim_remain,
employer_liab,
employee_liab,
ddn_fee,
other_ins_payment,
remit_amount,
claim_status,
emp_batch_id,
prac_batch_id,
remit_date)

(select
'D',
ClaimID,
prac_id,
prov_id,
loc_id,
region_id,
employer_id,
employee_id,
patient_id,
plan_id,
claim_date,
par_claim,
non_par_fee,
pre_claim_used,
pre_claim_remain,
employer_liab,
employee_liab,
ddn_fee,
other_ins_payment,
remit_amount,
claim_status,
emp_batch_id,
prac_batch_id,
remit_date
from claimmaster C
where C.prac_batch_id = @BatchID and C.prac_batch_id = @PracticeID)


insert into #AllClaims (
claim_type,
ClaimID,
prac_id,
prov_id,
loc_id,
region_id,
employer_id,
employee_id,
patient_id,
plan_id,
claim_date,
par_claim,
non_par_fee,
pre_claim_used,
pre_claim_remain,
employer_liab,
employee_liab,
ddn_fee,
other_ins_payment,
remit_amount,
claim_status,
emp_batch_id,
prac_batch_id,
remit_date)

(select
'O',
ClaimID,
prac_id,
prov_id,
loc_id,
region_id,
employer_id,
employee_id,
patient_id,
plan_id,
claim_date,
par_claim,
non_par_fee,
pre_claim_used,
pre_claim_remain,
employer_liab,
employee_liab,
ddn_fee,
other_ins_payment,
remit_amount,
claim_status,
emp_batch_id,
prac_batch_id,
remit_date
from Orthomaster C
where C.prac_batch_id = @BatchID and C.prac_batch_id = @PracticeID)


select distinct C.*,D.*, P.* ,Q.*
from #AllClaims C, dependants D , Providers P, Practice_Master Q
where
C.prov_id = P.pro_id and
C.patient_id = D.de_id and
C.prac_id = Q.pr_id and
C.prac_batch_id = @BatchID and
c.prac_id = @PracticeID
order by C.claim_date
end
GO

 

[krishna]

Hi

Use "SET NoCount ON" as the first statement in SP. We will not get ado recordset if the above statement is not there in the SP. If i remember correctly this is a microsoft acknoweldged bug.

Regards
Krishna Kumar

 

[FrankPhillips]

Actually, through trial and error, it wasnt really the temp table at all that was the issue.

basically crystal reports doesnt like the insert/select statement.

Ended up having to go with a cursor, which kinda sucks cos the above is the way it is because i didnt want to do cursors in the first place, too tedious.

anyway, it works now, I had noticed that set no count on as well, but that was for if the (if i remember correctly) it cpf's or crashes vs.net.

tho, i was pretty steamed at the time i wrote the above and might have misread the info .

Anyway, next time i have a quck down and dirty table that i dont feel like doing with cursors, ill try that SET NoCount ON and let you know.

Thanks

 

[morriskc]

Frank,
I use stored procs (with many temp tables) alot & have found the best approach is to test by running the proc in SQL, like with SQL Programmer. If your proc comes back with more than one result set, Crystal gets very cranky. Even print statements can throw your report off.

 

[FrankPhillips]

morriskc,

actually, thats how i do it, basically execute the sp in query analyzer.

as for more than one result set, i had actually anticipated the fact that crystal reports wouldnt be able to handle it.

The problem as i said earlier, wasnt the stored procedure kicking out multiple result sets or even the temp table but the use of the select/insert i was using to populate the temp table instead of using a cursor.

crystal reports couldnt handle it so it bombed.

Thanks for the reply tho, appreciate the feedback.

Frank

 

[krishna]

Hi Frank

I am also using Temp table with Select/Insert. Things are working for me. Ofcouse I use Set No Count and the facts pointed out by morriskc ie

1) MY Sp returns only one recordset
2) I have removed all the print statement from SP

Regards
Krishna Kumar

 

[FrankPhillips]

Thanks,

Like I said tho, one recordset, no print statements.

When i get around to writing the next sp, ill try the nocount when i do the insert/select statement instead of a cursor. As it is, with a cursor, it works, and with deadlines comming up, im in a "if it aint broke dont fix it mode".

thanks again,


Frank