本文介绍了如何传递参数以便在存储过程中进行搜索的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
嗨..
以下是我使用游标的存储过程,
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
这篇关于如何传递参数以便在存储过程中进行搜索的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!