declare @s varchar(8000)
set @s=''
select @s=@s+',['+机型+']=sum(case 机型 when '''
+机型+''' then 1 else 0 end)'
from test where 代码='20040401' group by 机型
exec('select 序号,代理商'+@s+',总数=sum(1)
from(
select 代码=(select count(distinct 代理商) from test where 序号 <=a.序号)
,代理商,机型
from test where 代码=''20040401'' a
)a group by 序号,代理商')
set @s=''
select @s=@s+',['+机型+']=sum(case 机型 when '''
+机型+''' then 1 else 0 end)'
from test where 代码='20040401' group by 机型
exec('select 序号,代理商'+@s+',总数=sum(1)
from(
select 代码=(select count(distinct 代理商) from test where 序号 <=a.序号)
,代理商,机型
from test where 代码=''20040401'' a
)a group by 序号,代理商')
create proc p_qry
@代码 char(8)
as
declare @s varchar(8000)
set @s=''
select @s=@s+',['+机型+']=sum(case 机型 when '''+机型+''' then 1 else 0 end)'
from test where 代码=@代码
group by 机型
exec('select 序号=identity(int,1,1),代码,代理商'+@s+',总数=count(*)
into #t from test
where 代码='''+@代码+'''
group by 代码,代理商
select * from #t')
go--调用示例
exec p_qry '20040401'
rs.open "exec p_qry '20040401'",conn 'conn是adodb.connection对象或ADO连接字符串
create proc p_qry
@代码 char(8)
as
set nocount on
declare @s varchar(8000)
set @s=''
select @s=@s+',['+机型+']=sum(case 机型 when '''+机型+''' then 1 else 0 end)'
from test where 代码=@代码
group by 机型
exec('select 序号=identity(int,1,1),代码,代理商'+@s+',总数=count(*)
into #t from test
where 代码='''+@代码+'''
group by 代码,代理商
select * from #t')
go
代码='20040401' and 地区='重庆' and 时间>''.....
或 代码='20040401' and 时间>''.....然后在ASP中 tf="代码='20040401' and 地区='重庆' and 时间>''....."
在ASP中调用存储过程中运行 exec p_qry 'tj'
怎样更改这存储过程?
create proc p_qry
@条件 varchar(1000)=''
as
set nocount on
declare @s varchar(8000),@sql nvarchar(4000)
select @s='',@条件=case isnull(@条件,'') when '' then '' else ' where '+@条件 end
,@sql='select @s=@s+'',[''+机型+'']=sum(case 机型 when ''''''+机型+'''''' then 1 else 0 end)''
from test '+@条件+' group by 机型'
exec sp_executesql @sql,N'@s varchar(8000) out',@s out
exec('select 序号=identity(int,1,1),代码,代理商'+@s+',总数=count(*)
into #t from test '+@条件+' group by 代码,代理商
select * from #t')
go
我在数据库建有两个过程p_hz,p_hz2,在SQL服务库上运行两个都正常
create proc p_hz
@代码 char(8)
as
set nocount on
declare @s varchar(8000)
set @s=''
select @s=@s+',['+机型+']=sum(case 机型 when '''+机型+''' then 1 else 0 end)'
from test where 代码=@代码
group by 机型
exec('select 序号=identity(int,1,1),代码,代理商'+@s+',总数=count(*)
into #t from test
where 代码='''+@代码+'''
group by 代码,代理商
select * from #t')
gocreate proc p_hz2
@条件 varchar(1000)=''
as
set nocount on
declare @s varchar(8000),@sql nvarchar(4000)
select @s='',@条件=case isnull(@条件,'') when '' then '' else ' where '+@条件 end
,@sql='select @s=@s+'',[''+机型+'']=sum(case 机型 when ''''''+机型+'''''' then 1 else 0 end)''
from test '+@条件+' group by 机型'
exec sp_executesql @sql,N'@s varchar(8000) out',@s out
exec('select 序号=identity(int,1,1),代码,代理商'+@s+',总数=count(*)
into #t from test '+@条件+' group by 代码,代理商
select * from #t')
go我在ASP中分别调用上两个过程,调用第一个会出错,第二个却出错
<%
tj="序号='20040303' "
set rs=server.createobject("adodb.recordset")
sql = "exec p_hz '"&tj&"' "
rs.open sql,conn,1,1
%><%
tj2="20040303"
set rs2=server.createobject("adodb.recordset")
sql2 = "exec p_hz2 '"&tj2&"' "
rs2.open sql2,conn,1,1
%>
我在ASP中分别调用上两个过程,调用第一个会出错,第二个返回正常.
tj2="20040303"
set rs2=server.createobject("adodb.recordset")
sql2 = "exec p_hz2 '代码='"&tj2&"'"
rs2.open sql2,conn,1,1
%>
在以上存储过程中如过滤条件不止一条且不固定,如
代码='20040401' and 地区='重庆' and 时间>''.....
或 代码='20040401' and 时间>''.....然后在ASP中 tf="代码='20040401' and 地区='重庆' and 时间>''....."
在ASP中调用存储过程中运行 exec p_qry 'tj'
怎样更改这存储过程?
<%
tj="序号='20040303' "
set rs=server.createobject("adodb.recordset")
sql = "exec p_hz '"&tj&"' "
rs.open sql,conn,1,1
%>把 sql = "exec p_hz '"&tj&"' "
换成
sql="exec p_hz '序号='20040303''"也不行.
tj="序号=''20040303''"
set rs=server.createobject("adodb.recordset")
sql = "exec p_hz '"&tj&"' "
rs.open sql,conn,1,1
%>