--表zhiyeduty结构
item int
weekno varchar
begintime varchar
endtime varchar
timetype varchar
timedescribe varchar
char varchar--表zhiyeduty数据
--insert zhiyeduty(weekno,begintime,endtime,timetype,timedescribe) values('1','08:00:00','08:08:08','99','test duty time')
item weekno begintime endtime timetype timedescribe char
3 1 08:00:00 08:08:08 99 test duty time NULL
2 0 08:30:00 17:30:00 10 default duty time NULL--存储过程
--CREATE PROCEDURE sp_hw_zhiyeduty
@cp_time VARCHAR(8), --参数传入值无效,在存做了CONVERT getdate() 108赋值
@cp_return TINYINT OUTPUT
AS
BEGIN
SET @cp_time = CONVERT(VARCHAR(8),getdate(),108)
--SET @cp_time = '06:06:06' --for test
IF EXISTS (SELECT 1 FROM zhiyeduty WHERE begintime < @cp_time and endtime > @cp_time )
SET @cp_return = 1 --上班时间
ELSE
SET @cp_return = 2 --非上班时间
END
GO--declare @outcode1 TINYINT, @outcode2 TINYINT
exec sp_hw_zhiyeduty '08:07:06', @outcode1 output
select @outcode1 --1
exec sp_hw_zhiyeduty '08:09:10', @outcode2 output
select @outcode2 --1----如何构建sp IF EXISTS获取@outcode2为2非上班时间??????
item int
weekno varchar
begintime varchar
endtime varchar
timetype varchar
timedescribe varchar
char varchar--表zhiyeduty数据
--insert zhiyeduty(weekno,begintime,endtime,timetype,timedescribe) values('1','08:00:00','08:08:08','99','test duty time')
item weekno begintime endtime timetype timedescribe char
3 1 08:00:00 08:08:08 99 test duty time NULL
2 0 08:30:00 17:30:00 10 default duty time NULL--存储过程
--CREATE PROCEDURE sp_hw_zhiyeduty
@cp_time VARCHAR(8), --参数传入值无效,在存做了CONVERT getdate() 108赋值
@cp_return TINYINT OUTPUT
AS
BEGIN
SET @cp_time = CONVERT(VARCHAR(8),getdate(),108)
--SET @cp_time = '06:06:06' --for test
IF EXISTS (SELECT 1 FROM zhiyeduty WHERE begintime < @cp_time and endtime > @cp_time )
SET @cp_return = 1 --上班时间
ELSE
SET @cp_return = 2 --非上班时间
END
GO--declare @outcode1 TINYINT, @outcode2 TINYINT
exec sp_hw_zhiyeduty '08:07:06', @outcode1 output
select @outcode1 --1
exec sp_hw_zhiyeduty '08:09:10', @outcode2 output
select @outcode2 --1----如何构建sp IF EXISTS获取@outcode2为2非上班时间??????
SET @cp_return = 1 --上班时间
ELSE
SET @cp_return = 2 --非上班时间
create table zhiyeduty(
item int identity(1,1),
weekno varchar(20),
begintime varchar(10),
endtime varchar(10),
timetype varchar(10),
timedescribe varchar(20),
char varchar(10)
)
insert zhiyeduty(weekno,begintime,endtime,timetype,timedescribe) values('1','08:00:00','08:08:08','99','test duty time')
insert zhiyeduty(weekno,begintime,endtime,timetype,timedescribe) values('0','08:30:00','17:30:00','10','default duty time')
go
create function sp_hw(@begintime varchar(10),@endtime varchar(10),@cp_time varchar(10))
returns nvarchar(10)
as
begin
if @cp_time>=@begintime and @cp_time<=@endtime
return '上班时间'
else
return '非上班时间'
return 0
end
go
select weekno,timetype,timedescribe,dbo.sp_hw(begintime,endtime,'16:06:06') as say from zhiyeduty
/*
weekno timetype timedescribe say
-------------------- ---------- -------------------- ----------
1 99 test duty time 非上班时间
0 10 default duty time 上班时间(2 行受影响)*/
go
drop function dbo.sp_hw
drop table zhiyeduty
exec sp_hw_zhiyeduty '08:09:10', @outcode2 output
不知该怎么建立这样的存储过程(关键就那if exists里的查询语句)。
SET @cp_time = CONVERT(VARCHAR(8),getdate(),108)
--SET @cp_time = '06:06:06' --for test
IF EXISTS (SELECT 1 FROM zhiyeduty WHERE begintime < @cp_time and endtime > @cp_time )
SET @cp_return = 1 --上班时间
ELSE
SET @cp_return = 2 --非上班时间
END你这句红色的导致传入的@cp_time都废了,永远是你的当前时间!我勒个去!
谢谢各位。