Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
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
    --*********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 25-Dec-12 23:10pm
Edited 25-Dec-12 23:14pm
v2
Comments
Priyaaammu at 26-Dec-12 5:22am
   
Yes...have to filter records with that input parameters
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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
WHERE DATEDIFF(Day, DOJ, @doj) = 0
Refer this[^] link for information on datetime comparison.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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
  Permalink  
v2
Comments
Priyaaammu at 26-Dec-12 5:40am
   
Thanks...but at which place i have to use where clause,can u explain little brief?
Priyaaammu at 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."
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
v2
Comments
Priyaaammu at 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
Robot With Defects at 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)



Advertise | Privacy | Mobile
Web04 | 2.8.141022.2 | Last Updated 26 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100