calling stored procedure
Hello Techies
I want to call a stored procedure which does not return anything but it executes the query inside itself.
How should I take the output from it 朓 want 3 values from that procedure.
The stored procedure is:::::::
CREATE Procedure NewPFAttendance_Register
(
@fromdate varchar(25),
@todate varchar(25),
@Ecode nvarchar(200)
)
As
SET NOCOUNT ON
Declare @l varchar(10)
Declare @todate1 datetime
Declare @fromdate1 datetime
set @todate1=convert(varchar(25),@todate)
set @fromdate1=convert(varchar(25),@fromdate)
Create table #tempdate (tdate datetime,ecode nvarchar(20))
Create table #Tcode (empcode nvarchar(30))
Create table #Details(empcode nvarchar(30),wdays int,pdays int,adays int,ldays int)
Create table #tempmon(mon nvarchar(20),yr int)
Create table #final(ecode nvarchar(20),mon nvarchar(20),yr int,nodays int,mindate datetime,maxdate datetime,wdays int,pdays int)
Create table #finalP(ecode nvarchar(20),pdays int)
if datediff(d,@todate1,@fromdate1)>0
raiserror ('Todate should be greater then from date',18,1)
while datediff(d,@todate1,@fromdate1)<=0
begin
insert into #tempdate values (@fromdate1,@Ecode)
set @fromdate1=dateadd (d,1,@fromdate1)
end
Declare @Totalmon int, @MName nvarchar(20)
Set @Totalmon=DateDiff(month,@fromdate,@todate)
Declare @i int
Set @i=0
while (@Totalmon>=0)
begin
insert into #tempmon(mon,yr) values (DATENAME(month,@fromdate1),DATENAME(year,@fromdate1))
Set @Totalmon=@Totalmon-1
Set @fromdate1=dateadd (mm,-1,@fromdate1)
end
-- Added By Prashant on 11 Oct 2005
insert into #Tcode values (@ECODE)
--End
Select a.empcode,a.name,tdate,
Case when (select timein from tblattendance c where c.empcode=a.empcode
and c.tsdate=b.tdate )is null
then
Case when (select purpose from tblholiday a where a.tsdate=b.tdate )is null
then
Case when (select leave from tblleave l where l.empcode=a.empcode and
l.tsdate=b.tdate )is null then 'A'
else
(select leave from tblleave l where l.empcode=a.empcode and
l.tsdate=b.tdate )--is null then 'A'--'L'
end
else
'H'
end
else
'P'
end
as intime,a.designation,a.DOJ
into #umang
from tblcode a,#tempdate b order by a.empcode
--,#TCode d where d.empcode=a.empcode and b.tdate>=d.tcdate order by a.empcode
alter table #umang alter COLUMN intime varchar(25)
update #umang set intime='Sat' where datepart (dw,tdate)=7
update #umang set intime='Sun' where datepart (dw,tdate)=1
Declare @Predate datetime
Declare Present1 Cursor for
Select tdate from #Tempdate
Open Present1
fetch next from Present1 into @Predate
Declare @Cnt int
Set @Cnt=0
while @@fetch_status=0
begin
--Print @Predate
if (datepart(dw,@Predate)=7 or datepart(dw,@Predate)=1)
begin
Set @Cnt=@Cnt+1
end
fetch next from Present1 into @Predate
end
CLOSE Present1
Deallocate Present1
Declare @Workday int ,@TotalDays Int
set @Workday=dbo.fWorkingDaysBetween(@fromdate,@todate)
Set @TotalDays=DateDiff(day,@fromdate,@todate)
--Print @TotalDays
Declare @Pre nvarchar(25)
Declare Present Cursor for
Select empcode from #TCode
Open Present
fetch next from Present into @Pre
Declare @Pdays int
Declare @Ldays int
Declare @Adays int
Declare @Adays1 int
Declare @Adays2 int
Declare @Adays3 int
while @@fetch_status=0
begin
Set @Pdays=(select count(timein) from tblattendance a,#TCODE b where tsdate between @fromdate and @todate and a.empcode=b.empcode and a.empcode=@Pre and datepart(dw,tsdate)<>7 and datepart(dw,tsdate)<> 1)
Set @Ldays=(select count(*) from tblleave a,#TCODE b where tsdate between @fromdate and @todate and a.empcode=b.empcode and a.empcode=@Pre )
Set @Adays=@Workday-(@Pdays+@Ldays)
DECLARE @final int
Set @final=(@TotalDays-@Adays)
insert into #Details values(@Pre,@WorkDay,@Pdays,@Adays,@Ldays)
fetch next from Present into @Pre
end
CLOSE Present
Deallocate Present
insert into #final (ecode,mon,yr,nodays ,mindate ,maxdate ,wdays)
select ecode,DateName(month,max(tdate)),Datename(year,max(tdate)), count (tdate), min(tdate),max(tdate),dbo.fWorkingDaysBetween(min(tdate),max(tdate)) as working from #tempdate group by month(tdate),year(tdate),ecode
select * from #final
Declare @Dt1 datetime,@Dt2 datetime
Declare DtCal Cursor for
Select mindate,maxdate from #final
Open DtCal
fetch next from DtCal into @Dt1,@Dt2
while @@fetch_status=0
begin
Declare @Dtfin1 varchar(25)
Declare @Dtfin2 varchar(25)
set @Dtfin1=convert(varchar(25),@Dt1,101)
set @Dtfin2=convert(varchar(25),@Dt2,101)
--Print @Dt1
--Print @Dt2
--Print @Dtfin1
--Print @Dtfin2
Declare @Pldays int ,@Abdays int ,@Monnm nvarchar(20),@Yrnm int
Set @Pldays=(select count(timein) from tblattendance where tsdate between @Dtfin1 and @Dtfin2 and empcode=@Ecode and datepart(dw,tsdate)<>7 and datepart(dw,tsdate)<> 1 )
Set @Monnm=(select Datename(month,@Dt1))
Set @Yrnm=(select Datename(year,@Dt1))
update #final set Pdays=@Pldays where mon=@Monnm and yr=@Yrnm
--update #final set pdays='0' where mon='January' and yr='2005'
--insert into #finalP(ecode,pdays) values (@Ecode,@Pldays)
Print @Monnm
Print @Pldays
fetch next from DtCal into @Dt1,@Dt2
end
CLOSE DtCal
Deallocate DtCal
select * from #final
--select * from #final a, #finalP b where a.ecode= b.ecode and a.
--EXEC NewPFAttendance_Register '01/01/2005','03/02/2005','2854'
GO
My java code is :::::::
"In my code ,in line
sDate = cstmt.getString("mon");
"mon" is the column name"
/****************************************************************************/
>public String callStoredProcedureResultset(String startDate,String >endDate,String empId){
>
> CallableStatement cstmt = null;
> String sDate = null;
> try{
>
> cstmt =conn.prepareCall("{call >NewPFAttendance_Register(?,?,?)}");
> cstmt.setString(1,startDate);
>cstmt.setString(2,endDate);
>cstmt.setString(3,empId);
> ResultSet rs = cstmt.executeQuery();
>while(rs.next()){
>
> sDate = cstmt.getString("mon");
>
> System.out.println("Start Date in >callStoredProcedureResultset::::::"+sDate);
> cstmt.close();
>} }catch (Exception e){
> e.printStackTrace();}
>
> return sDate;
> }//End of callStoredProcedureResultset
