ALTER PROCEDURE [dbo].[GetSortData]
@condtions varchar(200),
@tbName varchar(50),
@starttime varchar(20)
AS
declare @str varchar(5000)
BEGIN
SET NOCOUNT ON;
SET ARITHABORT off;
SET ANSI_WARNINGS off; if object_id('dbo.sorttmp') is not null
drop table sorttmp
declare @p int
declare @sql nvarchar(4000)
set @p = CharIndex(',',@condtions)
if @p=0
begin
set @condtions=@condtions
end
else
begin
set @condtions = substring (@condtions, 1,@p-1 )
end set @sql='select * from '+@tbName+' where '+@condtions+' and (CONVERT(varchar(100), coltime, 20) like ''%'+@starttime+'%'')'
exec(@sql)
if @@rowCount>0
begin
set @sql='select * into sorttmp from '+@tbName+' where '+@condtions+' and (CONVERT(varchar(100), coltime, 20) like ''%'+@starttime+'%'')'
exec(@sql)
alter table sorttmp add sortcon varchar(200)
update sorttmp set sortcon='+@condtions+'
end
while @p>0
begin
set @condtions= substring (@condtions, @p+1,len(@condtions) )
set @p = CharIndex(',',@condtions)
if @p=0
begin
set @condtions=@condtions
end
else
begin
set @condtions = substring (@condtions, 1,@p-1 )
end
set @sql='select * from '+@tbName+' where '+@condtions+' and (CONVERT(varchar(100), coltime, 20) like ''%'+@starttime+'%'')'
exec(@sql)
if @@rowCount>0
begin
if object_id('dbo.tmp') is not null
drop table tmp
set @sql='select * into tmp from '+@tbName+' where '+@condtions+' and (CONVERT(varchar(100), coltime, 20) like ''%'+@starttime+'%'')'
exec(@sql)
alter table tmp add sortcon varchar(200)
update tmp set sortcon='+@condtions+'
insert into sorttmp select * from tmp
end
end
select * from sorttmp group by sortcon
END
@condtions varchar(200),
@tbName varchar(50),
@starttime varchar(20)
AS
declare @str varchar(5000)
BEGIN
SET NOCOUNT ON;
SET ARITHABORT off;
SET ANSI_WARNINGS off; if object_id('dbo.sorttmp') is not null
drop table sorttmp
declare @p int
declare @sql nvarchar(4000)
set @p = CharIndex(',',@condtions)
if @p=0
begin
set @condtions=@condtions
end
else
begin
set @condtions = substring (@condtions, 1,@p-1 )
end set @sql='select * from '+@tbName+' where '+@condtions+' and (CONVERT(varchar(100), coltime, 20) like ''%'+@starttime+'%'')'
exec(@sql)
if @@rowCount>0
begin
set @sql='select * into sorttmp from '+@tbName+' where '+@condtions+' and (CONVERT(varchar(100), coltime, 20) like ''%'+@starttime+'%'')'
exec(@sql)
alter table sorttmp add sortcon varchar(200)
update sorttmp set sortcon='+@condtions+'
end
while @p>0
begin
set @condtions= substring (@condtions, @p+1,len(@condtions) )
set @p = CharIndex(',',@condtions)
if @p=0
begin
set @condtions=@condtions
end
else
begin
set @condtions = substring (@condtions, 1,@p-1 )
end
set @sql='select * from '+@tbName+' where '+@condtions+' and (CONVERT(varchar(100), coltime, 20) like ''%'+@starttime+'%'')'
exec(@sql)
if @@rowCount>0
begin
if object_id('dbo.tmp') is not null
drop table tmp
set @sql='select * into tmp from '+@tbName+' where '+@condtions+' and (CONVERT(varchar(100), coltime, 20) like ''%'+@starttime+'%'')'
exec(@sql)
alter table tmp add sortcon varchar(200)
update tmp set sortcon='+@condtions+'
insert into sorttmp select * from tmp
end
end
select * from sorttmp group by sortcon
END
@condtions 是条件字符串,就是WHERE后面跟的条件语句,而且我程序中传递过来的参数是个用’,‘分割的字符串,分割开的N个不同的条件。
@tbName是数据表名的字符串。
@starttime是时间字符串,因为名为@tbName的数据表里有不同年度月份的数据。
存储过程大致流程是:
用@p来表示分隔号’,‘在@condtions字符串中的位置,然后@condtions = substring (@condtions, 1,@p-1 )截取第一个逗号之前的condtion,然后对名为@tbName的数据表做查询,查询结果集保存到表sorttmp,再在sorttmp加一列sortcon,将condtion写入到sortcon,表示刚才这些查询结果是根据sortcon列中保存的字符串来做的。
然后再用WHILE循环不断的截取字符串@condtions,每次将一个condtion截取出来做查询,然后再做上面的操作,只是将结果保存到表tmp中,然后再将TMP表中的数据INSERT到表sorttmp中。这样循环直到没有逗号为止,然后再做select * from sorttmp group by sortcon,得到想要的结果集。
可以这样用吗?
不太懂耶!