修改一下存储过程
CREATE procedure dbo.statSingleVisualize1as
begindeclare @sql varchar(8000)
set @sql='select sn,[name],theTime'
select @sql=@sql+',sum(case category when '''+category +''' then deduct else 0 end) ['+category+']'
from (select distinct category from tbVisualize) as a
select @sql=@sql+' from tbVisualize group by sn ,[name],theTime'exec(@sql)end
CREATE procedure dbo.statSingleVisualize1as
begindeclare @sql varchar(8000)
set @sql='select sn,[name],theTime'
select @sql=@sql+',sum(case category when '''+category +''' then deduct else 0 end) ['+category+']'
from (select distinct category from tbVisualize) as a
select @sql=@sql+' from tbVisualize group by sn ,[name],theTime'exec(@sql)end
解决方案 »
- select top"+pageSize+ " * from topic...总是提示from附近有语法错误,实在看不出来了
- 求某小时内的平均值
- 大家有没有遇到过因为网路问题而引起的SQL操作表错误?
- 简单问题解决给高分(70),在线等!2000系统下安装未成功,系统日志文件是这样写的:
- SQL高手帮忙看下!
- Sql 难题 我是菜鸟谁来帮我
- 能否into到一个正式表的同时创建这个正式表,而非临时表
- sql server 中linked server查询出错。
- 触发器问题!!!执行顺序!
- 问一个优化问题??
- pengdali(大力 V3.0) zjcxc(邹建) ( )等高手请进
- 问一下在企业管理器中在那里管理触发器???
CREATE procedure dbo.statSingleVisualize1
as
begin
set nocount on
declare @sql varchar(8000)
set @sql='select sn,[name],theTime'
select @sql=@sql+',sum(case category when '''+category +''' then deduct else 0 end) ['+category+']'
from (select distinct category from tbVisualize) as a
select @sql=@sql+' from tbVisualize group by sn ,[name],theTime'
exec(@sql)
set nocount off
end
GO
dim conn$conn=数据库连接字符串
rs.open "exec statSingleVisualize1",conn,1,1
'ASP中的调用方法:set rs=server.createobject("adodb.recordset")
conn=数据库连接字符串
rs.open "exec statSingleVisualize1",conn,1,1
我是这样做的
alter procedure dbo.statSingleVisualize1
(
@beginTime datetime,
@endTime dateTime,
@theSn varchar(30))as
begin
set nocount on
declare @sql varchar(8000)
set @sql='select sn,[name],theTime'
select @sql=@sql+',sum(case category when '''+category +''' then deduct else 0 end) ['+category+']'
from (select distinct category from tbVisualize) as a
select @sql=@sql+' from tbVisualize
where sn='+@theSn+' and theTime between '+@beginTime+' and '+@endTime+'
group by sn ,[name],theTime'exec(@sql)
set nocount off
end
GO调用:exec dbo.statSingleVisualize1 '2004-1-1','2004-1-10','2003430002'错误:
从字符串转换为 datetime 时发生语法错误。再请教,谢谢!
@beginTime datetime,
@endTime dateTime,
@theSn varchar(30)
as
begin
set nocount on
declare @sql varchar(8000)
set @sql='select sn,[name],theTime'
select @sql=@sql+',sum(case category when '''+category +''' then deduct else 0 end) ['+category+']'
from (select distinct category from tbVisualize) as a
select @sql=@sql+' from tbVisualize
where sn='+@theSn+' and theTime between '''
+convert(varchar,@beginTime,120)
+''' and '''+convert(varchar,@endTime,120)+'''
group by sn ,[name],theTime'exec(@sql)
set nocount off
end
GOexec dbo.statSingleVisualize1 '2004-1-1','2004-1-10','2003430002'
classID weekday sn subject
1 1 1 语文
1 1 2 数学
1 1 3 化学
......
1 1 6 体育
1 2 1 物理
1 2 2 英语
......
1 5 6 政治其中weekday是星期,sn是课程节次,subject 是课程想得到一个课程表
1 2 3 4 5 6
星期一
星期二
...请问如何实现?
刚入门,问题多,谢谢!