Show/List Output from parameterized Stored procedure in View

This public forum is for user-to-user discussions of ASP.NET Maker. Note that this is not support forum.
Post Reply
prem01628
User
Posts: 23

Show/List Output from parameterized Stored procedure in View

Post by prem01628 »

How can i get output of parameterized Stored Procedures (#temp tables are used in Stored Procedures) in MS SQL ServerAsp.net maker 2016 . Is there any in built system function or method to Show/List output data in view.


Webmaster
User
Posts: 9425

Post by Webmaster »

Can you post your stored procedure for discussion?


prem01628
User
Posts: 23

Post by prem01628 »

-- EXEC PERDB..ctc_payset 'STGEF','RJS','1','01',NULL,NULL,01,12

ALTER procedure [dbo].[ctc_payset]
@comapnyvarchar(20),
@loc varchar(20),
@lan_idvarchar(10),
@payset varchar(20),
@deptvarchar(20) ,
@emp_code varchar(20),
@fmonth int,
@tmonth int

as
begin
if isnull(@dept,'')=''
select @dept = null
if isnull(@emp_code, '')=''
select @emp_code = null
create table #temp
(
H01_EMP_NUM varchar(40),
H01_Contact_Name2 varchar(40),
h01_social_security_no varchar(20),
h01_social_insurance_no varchar(20),
DOB datetime,
DOJ datetime,
P08_Payset_Desc varchar(40),
H01_KNOWN_AS varchar(40),
DESIGNATION varchar(40),
h01_contact_name1 varchar(40),
h40_emp_status4 varchar(40),
basic float,
hra float,
ca float ,
gross float,
basic_bonus float,
bonus float,
basic_pf float,
pf float,
lww float,
cl float,
sl float,
esi_grossfloat,
esi float,
ctcfloat ,
DOLvarchar(20),
dept_namevarchar(50),
reim float,
medical float,
conv float,
enter float,
lib float,
driver float,
helper float,
lta float,
uni float,
total float,
Payset varchar(50),
RES_addressvarchar(200) ,
PER_address varchar(200) ,
emp_count float ,
ter_status char(1),
Hra_min float,
Ca_min float,
Cat varchar(20),
SMART_CARD VARCHAR(20),
eff_dt datetime ,
title varchar(10) ,
weekly_off varchar(20),
weekly_desc varchar(50)
)

insert into #temp
SELECT distinct b.H01_EMP_NUM, b.H01_Contact_Name2 ,
b.h01_social_security_no,b.h01_social_insurance_no,
b.h01_birth_date ,b.h01_join_date,
g.P08_Payset_Desc,
(isnull(H01_First_Name,'') +' '+ isnull(H01_Middle_Name,'') +' '+ isnull(H01_Last_Name,'')) as H01_KNOWN_AS,H.c12_positiondesc ,
b.h01_contact_name1,c.h40_emp_status4,
c.h10_base_salary,'','','','','' ,'','','','','','','','','','' ,'','',
'','','','', '','','','','' ,'','' ,'','N','','',c.c08_catg_code ,'' ,c.H10_Effective_Date_From ,b.H03_title ,'',''
FROM
h01_identfcatnB (nolock),
h10_empasgn c (nolock),
p15_payroll_empasgn f (nolock),
p08_payset g(nolock),
c12_position H(nolock)
WHERE
b.h01_status in ('C','R')
and b.h01_status <>'T'
AND B.H01_EFFECTIVE_DATE_TOIS NULL
and c.h10_effective_date_to is null
and datepart(mm,h01_join_date) between @fmonthand @tmonth
and p15_eff_process_period_to is null
and f.p08_payset_code in (select payset from payset_group (nolock)where code = @PAYSET)
and g.p08_payset_code in (select payset from payset_group (nolock)where code = @payset)
and b.h01_emp_num = f.h01_emp_num
and f.p06_payroll_code = g.p06_payroll_code
and f.p08_payset_code= g.p08_payset_code
and B.H01_EMP_NUM = c.H01_EMP_NUM
and H.c12_positioncode= C.c12_positioncode
andb.h01_emp_num= isnull(@emp_code,b.h01_emp_num)
group by
b.H01_EMP_NUM,b.H01_Contact_Name2,b.h01_social_security_no,b.h01_social_insurance_no,b.h01_birth_date
,b.h01_join_date,g.P08_Payset_Desc ,
(isnull(H01_First_Name,'') +' '+ isnull(H01_Middle_Name,'') +' '+ isnull(H01_Last_Name,'')),H.c12_positiondesc ,b.h01_contact_name1,c.h40_emp_status4,
c.h10_base_salary,g.p08_payset_desc,c.c08_catg_code ,c.H10_Effective_Date_From ,b.H03_title

update #temp
set payset = (select distinct payset_desc from payset_group (nolock)where code = @PAYSET)

update #temp
set hra = (select round((basic*30/100),0)
WHERE h01_emp_num <> 'MD0001' and #temp.cat <>'MIn')

update #temp
set hra = 0
where p08_payset_desc like ('%TRAINE%')--,'PLANT TRAINEES')

update #temp
set hra_min = round(isnull(a.hra,0),0) from min_wages_det a
where a.h01_emp_num = #temp.h01_emp_num and #temp.cat ='Min' and H10_Effective_Date_From = #temp.eff_dt


update #temp
set ca = (select round((basic*15/100),0)
WHERE h01_emp_num <> 'MD0001' and #temp.cat <> 'Min' )

update #temp
set ca = '1600'
where ca >= '1600'

update #temp
set ca = 0
where p08_payset_desc like ('%TRAINE%')

update #temp
set Ca_min = round(isnull(a.conv,0),0) from min_wages_det a
where a.h01_emp_num = #temp.H01_EMP_NUM and #temp.cat = 'Min' and H10_Effective_Date_From = #temp.eff_dt

update #temp
set hra = case isnull(#temp.hra,'') when '' then #temp.hra_min else #temp.hra endfrom #temp

update #temp
set ca = case isnull(#temp.ca,'') when '' then #temp.ca_min else #temp.ca endfrom #temp

update #temp
set gross = isnull(basic,0)+isnull(hra,0)+isnull(ca,0)

update #temp
set basic_bonus = basic
where p08_payset_desc not like ('%TRAINEE%')
and h01_emp_num not in ('WK0690','WK0067','MD0001')

update #temp
set basic_bonus = 3500
where basic_bonus >= 3500

update #temp
set bonus = round(basic_bonus*8.50/100,0)
where h01_emp_num not in ('WK0690','WK0067','MD0001')
and p08_payset_desc not like ('%TRAINE%')

update #temp
set basic_pf = basic
where h01_emp_num not in ('WK0690')

update #temp
set basic_pf = 15000
where basic_pf >= 15000
and h01_emp_num <>'WK0067'

update #temp
set basic_pf = 0
where p08_payset_desc like ('%TRAINEE%')

update #temp
set pf =round(basic_pf*12/100,0)
WHERE p08_payset_desc not like ('%TRAINE%')

and h01_emp_num not in ('WK0690')

update #temp
set pf = 1800
WHERE pf > = 1800
and h01_emp_num not in ('WK0067','MD0001')

update #temp
set lww = round(basic/30/12*15,0)
where h01_emp_num not in ('WK0067','MD0001')
and p08_payset_desc not like ('%TRAINE%')

update #temp
set cl =round(gross/30/12*7,0)
where h01_emp_num not in ('WK0067','MD0001')
and p08_payset_desc not like ('%TRAINEE%')

update #temp
set sl =round(gross/30/12*7,0)
where gross >= 21000
AND h01_emp_num not in ('WK0067','MD0001')
and p08_payset_desc not like ('%TRAINE%')

update #temp
set esi_gross = gross

update #temp
set esi_gross = 21000
where esi_gross >= 21000
AND h01_emp_num not in ('WK0067','MD0001')
and p08_payset_desc not like ('%TRAINEE%')

update #temp
set esi =round(esi_gross*4.75/100,0)
where sl = '0'
AND h01_emp_num not in ('WK0067','MD0001')
and p08_payset_desc not like ('%TRAINEE%')

update #temp
set medical = (select max(medical)
from REIMBwhere h01_emp_code = #temp.h01_emp_num
and p03_process_period = (select max(p03_process_period) from REIMB )
and medical > 0 )

update #temp
set conv = (select max(conv)
from REIMBwhere h01_emp_code = #temp.h01_emp_num
and p03_process_period = (select max(p03_process_period) from REIMB )
and conv > 0)

update #temp
set enter = (select max(enter)
from REIMBwhere h01_emp_code = #temp.h01_emp_num
and p03_process_period = (select max(p03_process_period) from REIMB )
and enter > 0)

update #temp
set lib = (select max(lib)
from REIMBwhere h01_emp_code = #temp.h01_emp_num
and p03_process_period = (select max(p03_process_period) from REIMB )
and lib > 0)

update #temp
set driver = (select max(driver)
from REIMBwhere h01_emp_code = #temp.h01_emp_num
and p03_process_period = (select max(p03_process_period) from REIMB )
and driver > 0)

update #temp
set helper = (select max(helper)
from REIMBwhere h01_emp_code = #temp.h01_emp_num
and p03_process_period = (select max(p03_process_period) from REIMB )
and helper > 0)

update #temp
set lta = (select max(lta)
from REIMBwhere h01_emp_code = #temp.h01_emp_num
and p03_process_period = (select max(p03_process_period) from REIMB )
and lta > 0)

update #temp
set uni = (select max(uni)
from REIMBwhere h01_emp_code = #temp.h01_emp_num
and p03_process_period = (select max(p03_process_period) from REIMB )
and uni > 0)

update #temp
set reim = round(ISNULL(medical,0)+ISNULL(conv,0)+ISNULL(enter,0)+ISNULL(lib,0)+ISNULL(driver,0)+ISNULL(helper,0)+ISNULL(lta,0)+ISNULL(uni,0),0)

update #temp
set ctc = isnull(basic,0)+isnull(hra,0)+isnull(ca,0)+isnull(bonus,0)+isnull(pf,0)+isnull(lww,0)
+isnull(cl,0)+isnull(sl,0)+isnull(esi,0)+isnull(medical,0)+isnull(conv,0)+isnull(enter,0)+
isnull(lib,0)+isnull(driver,0)+isnull(helper,0)+isnull(lta,0)+isnull(uni,0)

update #temp
set weekly_desc = case when #temp.weekly_off = 'WC001' Then 'SUNDAY WEEKLY OFF'
when #temp.weekly_off = 'MON01' Then 'MONDAY WEEKLY OFF'
when #temp.weekly_off = 'TUE01' Then 'THESDAY WEEKLY OFF'
when #temp.weekly_off = 'WED01' Then 'WEDNESDAY WEEKLY OFF'
when #temp.weekly_off = 'WC002' Then 'THRUSDAY WEEKLY OFF'
when #temp.weekly_off = 'FRI01' Then 'FRIDAY WEEKLY OFF'
when #temp.weekly_off = 'SAT01' Then 'SATURDAY WEEKLY OFF'
ELSE 'NOT ASIGN WEEKLY OFF'
END

update #temp
set DOL =e01_pay_thru_date
from #temp a,e01_exit_details b
where b.h01_emp_num = a.h01_emp_num

update #temp
set dept_name = a.c01_desc
from C01_Codedesc_Mas a ,h10_empasgn b
where b.h40_emp_status4 = a.c01_code and A.C01_TYPE = 'ES4'
ANDB.H01_EMP_NUM = #temp.H01_EMP_NUM
and h10_effective_date_to is null

update #temp
set emp_count = (select count(distinct #temp.h01_emp_num) from #temp)


update #temp
set RES_address = LTRIM(RTRIM(H02_ADDRESS1)) + ', ' + LTRIM(RTRIM(H02_ADDRESS2))+ ', ' + LTRIM(RTRIM(H02_ADDRESS3))+ ', '+
(SELECT LTRIM(RTRIM(MAX(c.C14_STATE_NAME )))
FROM C14_STATECODE c
WHERE C.C14_STATE_CODE = H2.C14_STATE_CODE)
FROMh02_address H2
WHERE H2.H02_ADDRESS_OF = #temp.H01_EMP_NUM
AND H2.H02_ADDRESS_TYPE = 'RES'
and h2.h02_effective_to is null

update #temp
set SMART_CARD =H41_ID_NO
from H41_IdDetails h
where h.h01_emp_num = #temp.H01_EMP_NUM
and h41_id_type ='ESI'

update #temp
set ter_status='Y'
from #temp a,h01_identfcatn b (nolock) ,e01_exit_details c (nolock)
where a.H01_EMP_NUM=b.H01_EMP_NUM
andb.H01_EMP_NUM=c.H01_EMP_NUM
andb.h01_effective_date_to is null
andh01_status<>'R'

select * from #temp

declare @tot_ter int
select @tot_ter=count(*) from #temp where ter_status='Y'

select @tot_ter tot_terMINATED,*, datepart(year,getdate())- datepart(year,dob) age ,datepart(year,getdate())- datepart(year,doj) tot_exp from #temp
end


when i execute the procedure with parameters its working fine ( EXEC PERDB..ctc_payset 'STGEF','RJS','1','01',NULL,NULL,01,12 )

I want to use output of the procedure in View / table format so that user list it and use search / sort / records per page features etc.
I can't use table because, if two or more users execute the procedure at same time the results will not correct.


Webmaster
User
Posts: 9425

Post by Webmaster »

Your stored procedure returns data from a temp table, you can list them, but you cannot search/sort/page the result like an existing table. If you want to search/sort/page, you'd better pass the search criteria, or sorting fields and sorting order, or page size and the starting page, to the stored procedure as input parameters so that the result will be already filtered/sorted/paged as you need.


prem01628
User
Posts: 23

Post by prem01628 »

Please give the detailed solution for list the above said output in page.


Webmaster
User
Posts: 9425

Post by Webmaster »

You can try db helper, see the example for Custom File in the help file. Change the SQL to EXEC command, e.g.

dbhelper.ExecuteHtml("EXEC ctc_payset 'STGEF','RJS','1','01',NULL,NULL,01,12", options");


Post Reply