动态sql增强问题
以下为相关表以及sp：
Create table Table_3 (id int, date1 date, score int)
insert into table_3 select 1, '2013-09-01',1 union all select 2,'2013-09-02',2
union all select 3,'2013-09-02', 2 union all select 4,'2013-09-02', 2 union all select 5,'2013-09-03', 3 union all select 6,'2013-09-03', 4
go
create proc usp_test @TableName varchar(100), @field varchar(50), @filter varchar(300), @func varchar(10)
as
begin
Declare @sql nvarchar(max)
declare @filterSql varchar(200)
if(@filter !='')
begin
set @filterSql = ' and '+@filter
end
set @sql = 'select '+ @field+','+@func + '(score) from '+ @TableName + ' where 1=1 ' + @filterSql + ' group by '+@field
exec sp_executesql @sql
End
go
usp_test 'Table_3','date1', 'Convert(date, Date1) =''2013-09-02''','sum'
use test
Create table Table_3 (id int, date1 datetime, score int)
insert into table_3 select 1, '2013-09-01',1
union all select 2,'2013-09-02',2
union all select 3,'2013-09-02', 2
union all select 4,'2013-09-02', 2
union all select 5,'2013-09-03', 3
union all select 6,'2013-09-03', 4
go
alter proc usp_test @TableName varchar(100), @field varchar(50), @filter varchar(300), @func varchar(10)
as
begin
Declare @sql nvarchar(max)
declare @filterSql varchar(200)
declare @filter1 varchar(200),@filter2 varchar(200)
if len(@filter )>0
begin
set @filter1=left(@filter,charindex('=',@filter)-1)
set @filter2=right(@filter,len(@filter)-charindex('=',@filter)+1)
if charindex('convert',@filter1)>0
begin
set @filter1= 'and '+@filter1
set @filter2='=(select '+@filter2+' from '+@tablename+')'
end
else
begin
set @filter1= 'and (select '+@filter1+' from '+@tablename+')'
set @filter2='='+@filter2
end
end
set @filterSql=@filter1+@filter2
set @sql = 'select '+ @field+','+@func + '(score) from '+ @TableName + ' where 1=1 ' + @filterSql + ' group by '+@field
print @sql
exec sp_executesql @sql
End
go
usp_test 'Table_3','date1', 'Convert(datetime, Date1)=Max(date1) ','sum'
