Hi..
Following is my stored procedure using cursor,
create procedure [dbo].[usp_SampleProcedure]
as
begin
declare @empname varchar(50)
declare @emplocation varchar(50)
declare @deptname varchar(50)
declare @basicsalary int
declare @hra int
declare @total int
declare @grade varchar(100)
declare @dob datetime
declare @doj datetime
declare cur_EmployeeDetails cursor for select * from view_EmployeeDetails
if exists (select * from sys.tables where sys.tables.name like '%#TempTable%')
begin
drop table #TempTable
end
else
begin
create table #TempTable(empname varchar(50),emplocation varchar(50),deptname varchar(50),basicsalary int,hra int,
netsalary int,grade varchar(50),dob datetime,doj datetime)
end
open cur_EmployeeDetails
begin
fetch cur_EmployeeDetails into @empname,@emplocation,@deptname,@basicsalary,@dob,@doj
while @@FETCH_STATUS=0
begin
set @hra=@basicsalary*23/100
set @total=@basicsalary + @hra
if(@total>17000)
begin
set @grade='A'
end
else
begin
set @grade='B'
end
insert into #TempTable (empname,emplocation,deptname,basicsalary,hra,netsalary,grade,dob,doj)
values(@empname,@emplocation,@deptname,@basicsalary,@hra,@total,@grade,@dob,@doj)
fetch cur_EmployeeDetails into @empname,@emplocation,@deptname,@basicsalary,@dob,@doj
end
end
close cur_EmployeeDetails
deallocate cur_EmployeeDetails
select
empname as EmpName,emplocation as EmpLocation,deptname as DeptName,basicsalary as BasicSalary,hra as HRA,
netsalary as Total,grade as Grade,convert (varchar(50),dob,103) as DOB,convert (varchar(50),doj,103) as DOJ
from #TempTable
end
Now i want to pass three input parameters as dept,dateofbirth and dateofjoining for searching options.
I dont know how to pass date as filter options...guide me