proc [dbo].[Att_showclassdeil] @month varchar(10),@dept varchar(100)
as
BEGIN
if exists (select * from tempdb..sysobjects where name like '#month_day' and type='U')
drop table #month_day
PRINT '1'
create table #month_day
(rq datetime )
if exists(select * from tempdb..sysobjects where name='#pb' and type='U')
drop table #pb
create table #pb
(userid varchar(20),
rq datetime,
uid int,
name varchar(10),
kqid varchar(10),
SUBNAME VARCHAR(100),
DEPTNAME VARCHAR(100)
)
declare @begind int
declare @begine int
declare @yearmonthdaybegin datetime
declare @yearmonthdayend datetime
set @yearmonthdaybegin= cast((@month+'-01') as datetime)
set @yearmonthdayend=dateadd(day,-1*datepart(day,dateadd(day,31,cast((@month+'-01') as datetime)) ),
dateadd(day,31,cast((@month+'-01') as datetime)) )set @begind=1
set @begine=datepart(day,dateadd(day,-1*datepart(day,dateadd(day,31,cast((@month+'-01') as datetime)) ),
dateadd(day,31,cast((@month+'-01') as datetime)) ))while (@begind <=@begine)
begin
insert into #month_day(rq) values (cast((@month+'-'+str(@begind)) as datetime))
set @begind=@begind+1
endinsert into #pb(userid,rq,uid,name,kqid,SUBNAME,DEPTNAME) select g.userid,g.rq,g.uid,k.name,k.kqid,K.SUBNAME,K.DEPTNAME
from (
select t.*,y.badgenumber from userinfo y,(
SELECT USERID,RQ,NUM_OF_RUN_ID AS UID FROM
(SELECT * FROM #MONTH_DAY D,(select distinct kkk.userid,a.startdate,A.enddate,A.NUM_OF_RUN_ID
from userinfo kkk left outer join (select * from user_of_run where startdate between @yearmonthdaybegin and @yearmonthdayend or enddate between @yearmonthdaybegin and @yearmonthdayend or @yearmonthdaybegin between startdate and enddate or @yearmonthdayend between startdate and enddate) a on kkk.userid=a.userid ) E
WHERE D.RQ BETWEEN E.STARTDATE AND E.ENDDATE or e.startdate is null ) K) t
where t.userid=y.userid) g
left outer join showptpperson k
on
g.badgenumber=k.kqid COLLATE Chinese_PRC_CI_AS order by kqid,rq
delete #pb FROM #PB A,(select * from showptpperson where beginworkday between @yearmonthdaybegin and @yearmonthdayend or endworkday between @yearmonthdaybegin and @yearmonthdayend) B
WHERE A.KQID=B.KQID COLLATE CHINESE_PRC_CI_AS AND (A.RQ < B.BEGINWORKDAY OR A.RQ > B.ENDWORKDAY )delete from #pb where userid in (select userid from userinfo where badgenumber collate chinese_prc_ci_as in (select kqid from showptpperson where beginworkday >@yearmonthdayend or endworkday <@yearmonthdaybegin))declare @sql varchar(8000)
set @sql='select kqid,max(name) as name,MAX(SUBNAME) AS SUBNAME,MAX(DEPTNAME) AS DEPTNAME,'
select @sql =@sql +'max(case when rq= '''+cast (rq as varchar(20))+''' then uid else null end) as '''+ltrim(str(datepart(day,rq)))+''',' from (select top 300 rq from #pb group by rq order by rq ) as a
select @sql = left(@sql,len(@sql)-1) + ' from #pb WHERE SUBNAME=' +''''+ @DEPT+''''+' OR DEPTNAME ='+''''+@DEPT+''''+' group by kqid order by kqid'
print @sql
exec(@sql)
END存储过程如上,我如果不用存储过程,直接定义变量执行,有结果。
但是我用存储过程,平常也可以,但是每天每月最后一天或第一天都不可以用,过了又可以了。
真是奇怪,请高手看看。
as
BEGIN
if exists (select * from tempdb..sysobjects where name like '#month_day' and type='U')
drop table #month_day
PRINT '1'
create table #month_day
(rq datetime )
if exists(select * from tempdb..sysobjects where name='#pb' and type='U')
drop table #pb
create table #pb
(userid varchar(20),
rq datetime,
uid int,
name varchar(10),
kqid varchar(10),
SUBNAME VARCHAR(100),
DEPTNAME VARCHAR(100)
)
declare @begind int
declare @begine int
declare @yearmonthdaybegin datetime
declare @yearmonthdayend datetime
set @yearmonthdaybegin= cast((@month+'-01') as datetime)
set @yearmonthdayend=dateadd(day,-1*datepart(day,dateadd(day,31,cast((@month+'-01') as datetime)) ),
dateadd(day,31,cast((@month+'-01') as datetime)) )set @begind=1
set @begine=datepart(day,dateadd(day,-1*datepart(day,dateadd(day,31,cast((@month+'-01') as datetime)) ),
dateadd(day,31,cast((@month+'-01') as datetime)) ))while (@begind <=@begine)
begin
insert into #month_day(rq) values (cast((@month+'-'+str(@begind)) as datetime))
set @begind=@begind+1
endinsert into #pb(userid,rq,uid,name,kqid,SUBNAME,DEPTNAME) select g.userid,g.rq,g.uid,k.name,k.kqid,K.SUBNAME,K.DEPTNAME
from (
select t.*,y.badgenumber from userinfo y,(
SELECT USERID,RQ,NUM_OF_RUN_ID AS UID FROM
(SELECT * FROM #MONTH_DAY D,(select distinct kkk.userid,a.startdate,A.enddate,A.NUM_OF_RUN_ID
from userinfo kkk left outer join (select * from user_of_run where startdate between @yearmonthdaybegin and @yearmonthdayend or enddate between @yearmonthdaybegin and @yearmonthdayend or @yearmonthdaybegin between startdate and enddate or @yearmonthdayend between startdate and enddate) a on kkk.userid=a.userid ) E
WHERE D.RQ BETWEEN E.STARTDATE AND E.ENDDATE or e.startdate is null ) K) t
where t.userid=y.userid) g
left outer join showptpperson k
on
g.badgenumber=k.kqid COLLATE Chinese_PRC_CI_AS order by kqid,rq
delete #pb FROM #PB A,(select * from showptpperson where beginworkday between @yearmonthdaybegin and @yearmonthdayend or endworkday between @yearmonthdaybegin and @yearmonthdayend) B
WHERE A.KQID=B.KQID COLLATE CHINESE_PRC_CI_AS AND (A.RQ < B.BEGINWORKDAY OR A.RQ > B.ENDWORKDAY )delete from #pb where userid in (select userid from userinfo where badgenumber collate chinese_prc_ci_as in (select kqid from showptpperson where beginworkday >@yearmonthdayend or endworkday <@yearmonthdaybegin))declare @sql varchar(8000)
set @sql='select kqid,max(name) as name,MAX(SUBNAME) AS SUBNAME,MAX(DEPTNAME) AS DEPTNAME,'
select @sql =@sql +'max(case when rq= '''+cast (rq as varchar(20))+''' then uid else null end) as '''+ltrim(str(datepart(day,rq)))+''',' from (select top 300 rq from #pb group by rq order by rq ) as a
select @sql = left(@sql,len(@sql)-1) + ' from #pb WHERE SUBNAME=' +''''+ @DEPT+''''+' OR DEPTNAME ='+''''+@DEPT+''''+' group by kqid order by kqid'
print @sql
exec(@sql)
END存储过程如上,我如果不用存储过程,直接定义变量执行,有结果。
但是我用存储过程,平常也可以,但是每天每月最后一天或第一天都不可以用,过了又可以了。
真是奇怪,请高手看看。
解决方案 »
- 跨表更新-有点复杂-求改写代码!
- SQL问题求解
- 求一条SQL语句????????????????????????
- 急!!!如何更新字符串的位
- 3表连接查询的SQL语句
- 我需要把一点sql的查询语句的内容自动导出到txt格式,要自动的不要手动操作sql的导出功能,请大家帮忙!!
- 如何删除表中所有带乱码的记录??? 急!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- 请问nvarchar类型的'2003-06-16 09:25'怎样转换成日期型,我不懂呀!
- 如何输入image数据
- 请教各位有关分区视图和分布式分区视图的问题。
- 在asp.net中 select top @pageRecord * from语句为何老提示 @num错误
- 求救一条约束如何写
另外问一下,
if exists (select * from tempdb..sysobjects where name like '#month_day' and type='U')
drop table #month_daycreate table #month_day
(rq datetime ) 这样应该是到查询分析器里执行多少次都可以的吧??
怎么我执行两次后就有提示错误如下:
消息 2714,级别 16,状态 6,第 4 行
数据库中已存在名为 '#month_day' 的对象。
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')--B. 月的最后一天
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')
drop table #month_day
go
create table #month_day
(rq datetime )
drop table #month_day create table #month_day
(rq datetime )
if object_id('#month_day') is not null
drop table #month_day
go
create table #month_day
(rq datetime )
drop table #month_day
go -- 少了个gocreate table #month_day
(rq datetime ) if object_id('#month_day') is not null
drop table #month_day
go
create table #month_day
(rq datetime )
set @yearmonthdaybegin= cast((@month+'-01') as datetime)
set @yearmonthdayend=dateadd(day,-1*datepart(day,dateadd(day,31,cast((@month+'-01') as datetime)) ),
dateadd(day,31,cast((@month+'-01') as datetime)) ) 用转化的效果也是一样的吧。
/*
当月第一天
----------
2009-01-01(所影响的行数为 1 行)
*/SELECT 当月最后一天 = dateadd(mm , 1 , CONVERT(varchar(7), getdate() , 120) + '-01') - 1
/*
当月最后一天
------------------------------------------------------
2009-01-31 00:00:00.000(所影响的行数为 1 行)
*/
SET @MONTH='2009-01'
DECLARE @yearmonthdaybegin DATETIME
DECLARE @yearmonthdayend DATETIME
set @yearmonthdaybegin= cast((@month+'-01') as datetime)
set @yearmonthdayend=dateadd(day,-1*datepart(day,dateadd(day,31,cast((@month+'-01') as datetime)) ),
dateadd(day,31,cast((@month+'-01') as datetime)) )
SELECT @yearmonthdaybegin,@yearmonthdayend
结果2009-01-01 00:00:00.000 2009-01-31 00:00:00.000
DECLARE @MONTH CHAR(20)
SET @MONTH='2009-01'
DECLARE @yearmonthdaybegin DATETIME
DECLARE @yearmonthdayend DATETIME
set @yearmonthdaybegin= cast((@month+'-01') as datetime)
set @yearmonthdayend=convert(char(10),dateadd(day,-1*datepart(day,dateadd(day,31,cast((@month+'-01') as datetime)) ),
dateadd(day,31,cast((@month+'-01') as datetime)) ) ,120)+' 23:59:59'
SELECT @yearmonthdaybegin,@yearmonthdayend ------------------------------------------------------ ------------------------------------------------------
2009-01-01 00:00:00.000 2009-01-31 23:59:59.000(所影响的行数为 1 行)
dateadd(day,31,cast((@month+'-01') as datetime)) )
应该不是这个问题
我同时加了31天的,这里加31-59应该结果都是一样的。
只是写成你那样要更好一点。
DECLARE @MONTH CHAR(20)
SET @MONTH='2009-02'
DECLARE @yearmonthdaybegin DATETIME
DECLARE @yearmonthdayend DATETIME
set @yearmonthdaybegin= cast((@month+'-01') as datetime)
set @yearmonthdayend=dateadd(day,-1*datepart(day,dateadd(day,31,cast((@month+'-01') as datetime)) ),
dateadd(day,31,cast((@month+'-01') as datetime)) )
SELECT @yearmonthdaybegin,@yearmonthdayend
结果:
2009-02-01 00:00:00.000 2009-02-28 00:00:00.000
DECLARE @MONTH CHAR(20)
SET @MONTH='2009-01'
DECLARE @yearmonthdaybegin DATETIME
DECLARE @yearmonthdayend DATETIME
set @yearmonthdaybegin= cast((@month+'-01') as datetime)
set @yearmonthdayend=dateadd(day,-1*datepart(day,dateadd(day,31,cast((@month+'-01') as datetime)) ),
dateadd(day,31,cast((@month+'-01') as datetime)) ) declare @t table(id int,d datetime)
insert @t select 1,getdate()
insert @t select 1,'2009-01-01 12:21:12'
insert @t select 1,'2009-01-31 12:21:12'
select * from @t
where d between @yearmonthdaybegin and @yearmonthdayend
/*
id d
----------- -----------------------
1 2009-01-01 14:10:09.660
1 2009-01-01 12:21:12.000(2 行受影响)
*/
set @yearmonthdayend=convert(char(10),dateadd(day,-1*datepart(day,dateadd(day,31,cast((@month+'-01') as datetime)) ),
dateadd(day,31,cast((@month+'-01') as datetime)) ) ,120)+' 23:59:59'
select * from @t
where d between @yearmonthdaybegin and @yearmonthdayend/*
id d
----------- -----------------------
1 2009-01-01 14:10:09.660
1 2009-01-01 12:21:12.000
1 2009-01-31 12:21:12.000(3 行受影响)*/
但是我只有用到日期部分。我现在的主要问题是,上面的代码在查询分析器里用T-SQL执行没错误,但是做成存储过程的时候,也没错误,但他会一直没反应,把BEGIN,END去了也是这样
难道有什么地方锁住了
用sp_who_lock也没看到有地方锁住。
--
输出结果正常吗?
1(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)
语句已终止。
用户取消了查询。谢谢happyflystone,谢谢各位。
明天再来加分。
select * from tempdb..sysobjects where name like '#month_day%' and type='U'
#month_day__________________________________________________________________________________________________________00000010738B 1406063805 U 1 1 1610612736 0 0 0 2009-01-01 11:46:09.453
#month_day__________________________________________________________________________________________________________000000107CFF 2014834050 U 1 1 1610612736 0 0 0 2009-01-01 14:02:21.283 drop table #month_day
消息 3701,级别 11,状态 5,第 1 行
无法 除去 表 '#month_day',因为它在系统目录中不存在。
godrop table #1