Click here to Skip to main content
15,887,971 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi..
Following is my stored procedure using cursor,
SQL
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
    --*********Cursor Open*********
      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
       --*********Cursor Closing*********
       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
Posted
Updated 25-Dec-12 23:14pm
v2
Comments
[no name] 26-Dec-12 5:18am    
Do u want to pass the parameters to the storedproc?
Priyaaammu 26-Dec-12 5:22am    
Yes...have to filter records with that input parameters

You can use DATEDIFF[^] to filter your records based on date.
If you want to retrieve the records whose DOJ match @DOJ value your where clause would look like
SQL
WHERE DATEDIFF(Day, DOJ, @doj) = 0

Refer this[^] link for information on datetime comparison.
 
Share this answer
 
Hi,

Modify your stored procedure like the following:

create procedure [dbo].[usp_SampleProcedure]
(
   //Define here your parameters that you want to pass as filter element.
   @dept INT,
   @dateofbirth datetime,
   @dateofjoining datetime
)
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 
.
.
.


These parameters will be passed from the code behind to the stored procedure during the execution of the stored procedure. And you can use the parameters in the where clause for filtering your data.

Thanks
 
Share this answer
 
v2
Comments
Priyaaammu 26-Dec-12 5:40am    
Thanks...but at which place i have to use where clause,can u explain little brief?
[no name] 26-Dec-12 5:46am    
Use your where clause in the select statement.
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 where (your conditions here)
Priyaaammu 26-Dec-12 6:11am    
I gave as,
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 where convert(varchar(50),@dateofbirth,103) between '05/05/1865' and '12/02/1989' and convert(varchar(50),@dateofjoining,103) between '08/03/1866' and '05/12/2012'

And while executing my procedure,i gave input as '08/15/1956' for dob and '03/08/1866' for doj...iam getting error as "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."
[no name] 26-Dec-12 7:41am    
Can you send the data types and table structure for the tables used in the stored procedure. That may be helpful.
At the end of your code there is a Select statement, just add where condition and filter based on these three fields.. ? is this what you required?

Another way:

add where condition to your select statement while creating the cursor itself..

Since you are not using any filters for displaying the data, passing null or blank values doesn't make any difference
 
Share this answer
 
v2
Comments
Priyaaammu 26-Dec-12 5:27am    
Yes...the following is my output without any input parameters
EmpName EmpLocation DeptName BasicSalary HRA Total Grade DOB DOJ
Priya Chennai Develpoment 12500 2875 15375 B 12/02/1989 05/12/2012
Nathan Bangalore Finance 17000 3910 20910 A 05/05/1865 12/04/1996
Vijay Mumbai Developer 14250 3277 17527 A 15/08/1956 08/03/1866

Then,how can i use filter with dob and doj parameters
Rakesh6906 26-Dec-12 5:38am    
**Updated in the Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900