存储过程检查日期例如，我想先检查开始日期是否大于到期日期，然后只想执行查询

Question

餐桌乘客

create table passenger
(
passport_no varchar(15) not null,
fname varchar(25) not null,
minit char(1),
lname varchar(25) not null,
gender char(1) not null,
nationalty varchar(50) not null,
dob date not null,
issue_of_pport date not null,
exp_of_pport date not null,
catagory varchar(10) not null,
acc_pport_no varchar(15),
constraint pk_passenger primary key (passport_no),
constraint fk_gauardian_passenger 
foreign key (acc_pport_no) references passenger(passport_no),                   //****//
constraint check_catagory 
check (catagory='adult' or catagory='senior' or catagory='child' or catagory='infant'),
constraint chech_gender_passenger 
check (gender = 'm' or gender = 'f')
)

我的存储过程

create or alter procedure find_passport_expiry_date
(@passport_id varchar(50),
@no_of_days int output)
as
begin
select @no_of_days = datediff(day, issue_of_pport, exp_of_pport) 
from passenger 
where passport_no = @no_of_days
end
declare @days int
exec find_passport_expiry_date '43fafea',@days output
select @days as 'no_of_days_until_expiry'

Answer 1

请参阅下面的

create or alter procedure find_passport_expiry_date
(@passport_id varchar(50),
@no_of_days int output)
as
begin
select @no_of_days = datediff(day, issue_of_pport, exp_of_pport) 
from passenger 
where passport_no = @passport_id 
end
if @no_of_days >0 
exec find_passport_expiry_date '43fafea',@days output