试对原Procedure作如下修改:
--------------------------------------------------------------------------------------
/*
set @sql='CREATE TABLE temp ('
DECLARE UserName CURSOR FOR select UserName
OPEN UserName
FETCH NEXT FROM UserName INTO @UserNameif (@@FETCH_STATUS = 0)
set @sql= @sql+ @UserName+ ' int DEFAULT 0'
FETCH NEXT FROM UserName INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql= @sql+','+ @UserName+ ' int DEFAULT 0 '
FETCH NEXT FROM UserName INTO @UserName
end
CLOSE UserName
DEALLOCATE UserName
*/--以上代码替换为以下四行代码
set @sql = ''
select @sql = @sql + ',['+UserName+'] int defualt 0 ' from dbo.UserMess
set @sql = stuff(@sql,1,1,'')
set @sql= 'CREATE TABLE temp (' +@sql+',总数 int DEFAULT 0 ,时段 varchar(50) null,时间 datetime )' --增加对表temp是否已经存在的判断
if exists(select 1 from sysobjects where id = object_id('temp'))
drop table temp--------------------------------------------------------------------------------------
BTW:使用游标太频繁,感觉优化的可能性比较大
--------------------------------------------------------------------------------------
/*
set @sql='CREATE TABLE temp ('
DECLARE UserName CURSOR FOR select UserName
OPEN UserName
FETCH NEXT FROM UserName INTO @UserNameif (@@FETCH_STATUS = 0)
set @sql= @sql+ @UserName+ ' int DEFAULT 0'
FETCH NEXT FROM UserName INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql= @sql+','+ @UserName+ ' int DEFAULT 0 '
FETCH NEXT FROM UserName INTO @UserName
end
CLOSE UserName
DEALLOCATE UserName
*/--以上代码替换为以下四行代码
set @sql = ''
select @sql = @sql + ',['+UserName+'] int defualt 0 ' from dbo.UserMess
set @sql = stuff(@sql,1,1,'')
set @sql= 'CREATE TABLE temp (' +@sql+',总数 int DEFAULT 0 ,时段 varchar(50) null,时间 datetime )' --增加对表temp是否已经存在的判断
if exists(select 1 from sysobjects where id = object_id('temp'))
drop table temp--------------------------------------------------------------------------------------
BTW:使用游标太频繁,感觉优化的可能性比较大
解决方案 »
- 帮忙看一sql语句
- 大侠进 救急。。。。。。。。。。。。。。
- 菜鸟送分了 SQL一个关于重复数据的问题
- 如何数据库数据表中的ID号用完将会怎样?
- 发现 sqlserver Enterprise Manager 创建constraint时候的一个bug
- sql server 2000 删除一张大表提示日志已满,还有数据库文件在的磁盘没有空间了,该怎么办?
- 两个表之间比较
- 行转列问题,做报表使用,有数据结构,求高人赐教
- 向变量赋值的select不能与数据检索一起使用,是什么错误?
- sql 远程访问奇怪问题
- 我有2000个客户端,如何在SQLserver里增加一条数据,让每个客户端都显示,在互联网上使用!!!
- 如何均匀的按某一字段分类取到等数的记录
而且又来了:第一行','附近有语法错误
小弟实在是搞不懂。
DECLARE UserName CURSOR FOR select UserName from dbo.UserMess
OPEN UserName
FETCH NEXT FROM UserName INTO @UserName
if (@@FETCH_STATUS = 0)
set @sql= @sql+ @UserName+ ' int DEFAULT 0'
FETCH NEXT FROM UserName INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql= @sql+','+ @UserName+ ' int DEFAULT 0 '
FETCH NEXT FROM UserName INTO @UserName
end
CLOSE UserName
DEALLOCATE UserName
set @sql= @sql+',总数 int DEFAULT 0 ,时段 varchar(50) null,时间 datetime )'
exec(@sql)
---上面这么一大段就是为了动态生成表temp,
但是你用的是exec(@sql)那么当跳出exec后,temp就消失了,因为他是一个相对的局部变量,下面你再引用temp当然报找不到temp了.
---[USER1] int default 0,[USER2] int default 0,[USER3] int default 0,......[USER15] in default 0---
set @sql = stuff(@sql,1,1,'')/*
set @sql= 'CREATE TABLE temp (' +@sql+',总数 int DEFAULT 0 ,时段 varchar(50) null,时间 datetime )'
.
.
.
.
.
*/
if object_id('tt') is not null
drop table tt
gocreate table tt(classid varchar(10))
goinsert into tt
select 101
union
select 102
union
select 103
union
select 104
union
select 105
union
select 106
union
select 107
union
select 108
union
select 109
declare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',['+classid+'] int default 0 ' from tt
set @sql = stuff(@sql,1,1,'')
set @sql= 'CREATE TABLE temp (' +@sql+',总数 int DEFAULT 0 ,时段 varchar(50) null,时间 datetime )'
print @sqlif object_id('temp') is not null
begin
drop table temp
endexec(@sql)select * from temp
print @sql我去掉了运行也正常但print @sql对ASP里面有影响如加上了print @sql,在ASP里显示的是@sql的内容 如果不要print @sql,则ASP提示找不到TEMP对象
@btime datetime,
@etime datetime,
@flag int
as
declare @temptime datetime
declare @temptime1 datetime
declare @mydate datetime
declare @UserName varchar(50)
declare @sql varchar(500)
declare @str varchar(10)
declare @icount int
declare @i int
set @icount=0
set @sql = ''
select @sql = @sql + ',['+UserName+'] int default 0 ' from dbo.UserMess
set @sql = stuff(@sql,1,1,'')
set @sql= 'CREATE TABLE temp (' +@sql+',总数 int DEFAULT 0 ,时段 varchar(50) null,时间 datetime )'
if object_id('temp') is not null
begin
drop table temp
end
exec(@sql)
set @temptime=@btime while (@temptime< @etime)
begin
insert into temp(时间) values(@temptime)
if (@flag=1)
set @temptime=DATEADD(day, 1, @temptime)
if (@flag=2)
set @temptime=DATEADD(Month, 1, @temptime)
end
----------------
---读出TEMP表的数据。用时间字段查数据
set @temptime=@btimeDECLARE mydate CURSOR FOR select 时间 from dbo.temp
open mydate
FETCH NEXT FROM mydate INTO @mydate
WHILE @@FETCH_STATUS = 0
BEGIN
--更新TEMP的对应列
DECLARE UserName CURSOR FOR select UserName from dbo.UserMess
OPEN UserName
FETCH NEXT FROM UserName INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
if (@flag=1)
set @temptime1=DATEADD(day, 1, @mydate)
if (@flag=2)
set @temptime1=DATEADD(Month, 1, @mydate)
select @icount=0
--select convert(varchar,@temptime), convert(varchar,@temptime1)
select @icount= count(*) from vcase where username=@UserName and TelComeTime between @mydate and @temptime1
set @sql='update temp set '+@UserName+'='+convert(varchar(4),@icount)+' where 时间='''+convert(varchar,@mydate)+''''
exec(@sql)
FETCH NEXT FROM UserName INTO @UserName
end
CLOSE UserName
DEALLOCATE UserName
-----------
FETCH NEXT FROM mydate INTO @mydate
end
CLOSE mydate
DEALLOCATE mydate
--TEMP中的整理数据
DECLARE mydate CURSOR FOR select 时间 from dbo.temp
open mydate
FETCH NEXT FROM mydate INTO @mydate
WHILE @@FETCH_STATUS = 0
BEGIN
if (@flag=1)
set @str=convert(varchar,year(@mydate))+'-'+convert(varchar,month(@mydate))+'-'+convert(varchar,day(@mydate))
if (@flag=2)
set @str=convert(varchar,year(@mydate))+'-'+convert(varchar,month(@mydate))
UPdATE TEMP SET 时段=@str where 时间= @mydateFETCH NEXT FROM mydate INTO @mydate
END
CLOSE mydate
DEALLOCATE mydate
--统计每行的总数 DECLARE UserName CURSOR FOR select UserName from dbo.UserMess
OPEN UserName
FETCH NEXT FROM UserName INTO @UserName
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql='update temp set 总数=总数+'+@UserName
exec(@sql) set @icount= @icount+@i
FETCH NEXT FROM UserName INTO @UserName
end
CLOSE UserName
DEALLOCATE UserName
----------------select * from temp
DROP TABLE temp
GO在SQL里已调试成功了
但在ASP里一直报错,说对象名 'temp' 无效<%
set rs = server.CreateObject("adodb.recordset")
sql="p7 [2004-12-1], [2005-1-1], [2]"
conn.cursorlocation = 3
set rs=server.createobject("adodb.recordset")
rs.open sql,conn,3,3
%>这是我最近最后一个问题,请帮帮我
while (@temptime< @etime)
begin
insert into temp(时间) values(@temptime)
if (@flag=1)
set @temptime=DATEADD(day, 1, @temptime)
if (@flag=2)
set @temptime=DATEADD(Month, 1, @temptime)
end特别怀疑insert into temp(时间) values(@temptime)这句话