Stored procedure with results

Question

Stored procedure with results

phelisamoss

Grasshopper

Points: 13
More actions
July 21, 2014 at 6:37 am

#291344

Hi everyone
I am new on the stored procedure area and I have created a stored procedure that returns results but when I execute the stored procedure nothing is returned. I have created the columns for the results to be displayed.
Your help will be greatly appreciated, thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Not enough information. Can you post the stored procedure code?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

phelisamoss Grasshopper Points: 13 More actions · Answer 2

Create or replace procedure pmoss.sp_cognos_rpt_mrm()

RESULT ( rpt_scheme_name varchar(60),

rpt_year integer,

rpt_month integer,

rpt_month_desc char(3),

rpt_dsp varchar(30),

rpt_enrollees integer,

rpt_perc_enrolled numeric(3, 2),

rpt_utilising_beneficiaries integer,

rpt_utilisation_rate numeric (12, 2),

rpt_items_utilising_beneficiary numeric(12, 2),

rpt_cost_item numeric(12, 2),

rpt_cost_utilising_beneficiary numeric(12, 2),

rpt_cost_enrolled_beneficiary numeric(12, 2),

rpt_cost_life numeric(12, 2),

rpt_cost_main_Member numeric(12, 2),

rpt_scheme_lives integer,

rpt_main_members integer,

rpt_dependants integer,

rpt_benefit_paid numeric(12, 2),

rpt_savings_amount numeric(12, 2),

rpt_scheme_risk numeric(12, 2),

rpt_items numeric(12, 2))

BEGIN

select li.scheme_name,

li.cal_year as [Year],

li.cal_mth_no as [Month],

isnull(li.cal_mth_long_name, '') as [Month Desc],

isnull(dsp_group,'') as DSP,

2 as srt,

au.chronic_lives as [Enrollees],

[Enrollees]/li.lives as [% Enrolled],

cl.claimants as [Utilising Beneficiaries],

([Utilising Beneficiaries] / cast([Enrollees] as numeric(12,2))) as [Utilisation Rate],

([Items] / cast([Utilising Beneficiaries] as numeric(12,2))) as [Items/Utilising Beneficiary],

([Scheme Risk] / [Items]) as [Cost/Item],

([Scheme Risk] / [Utilising Beneficiaries]) as [Cost/Utilising Beneficiary],

([Scheme Risk] / [Enrollees]) as [Cost/Enrolled Beneficiary],

([Scheme Risk] / [lives]) as [Cost/Life],

([Scheme Risk] / [mem_cnt]) as [Cost/Main Member],

li.lives as [Scheme Lives], li.mem_cnt as [Main Members], li.dep_cnt as [Dependants],

cl.benefit_amt as [Benefit Paid], cl.savings_amt as [Savings Amount], cl.risk_amt as [Scheme Risk],

cl.items as [Items]

from #scheme_lives li

left join #auth_summary au on li.cal_year = au.cal_year and li.cal_mth_no = au.cal_mth_no

left join #claims_summary cl on li.cal_year = cl.cal_year and li.cal_mth_no = cl.cal_mth_no

order by DSP, li.cal_year, li.cal_mth_no;

END;

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Oracle, by any chance?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

phelisamoss Grasshopper Points: 13 More actions · Answer 4

phelisamoss

Grasshopper

Points: 13

July 21, 2014 at 6:56 am

#1730991

Am using Sybase