如何传递参数以便在存储过程中进行搜索 [英] how to pass parameters for searching in stored procedure

问题描述

嗨..

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

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

推荐答案

你可以使用 DATEDIFF [ ^ ]根据日期过滤你的记录。

如果你想检索DOJ与@DOJ值相匹配的记录你的where子句看起来像是

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.

您好，



修改您的存储过程，如下所示：

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

这些参数将在执行存储过程期间从代码传递到存储过程。您可以使用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

在代码的末尾那里是一个Select语句，只需根据这三个字段添加where条件和过滤器。？这是你需要的吗？



另一种方式：



在创建时添加select语句的条件光标本身..



由于您没有使用任何过滤器来显示数据，传递空值或空值并没有任何区别

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

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