本文介绍了如何传递参数以便在存储过程中进行搜索的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

嗨..

以下是我使用游标的存储过程,

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



现在我想传递三个输入参数作为dept,dateofbirth和dateofjoining来搜索选项。

我不知道如何将日期作为过滤器选项传递...指导我


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

推荐答案

WHERE DATEDIFF(Day, DOJ, @doj) = 0



参考 []链接获取有关日期时间比较的信息。


Refer this[^] link for information on datetime comparison.


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
.
.
.





这些参数将在执行存储过程期间从代码传递到存储过程。您可以使用where子句中的参数来过滤数据。



谢谢



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



这篇关于如何传递参数以便在存储过程中进行搜索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-05 20:44
查看更多