create proc kk(
@spnumber varchar(20),
@cooperatorid varchar(20),
@begindate varchar(20),
@enddate varchar(20)
)
as
declare @sql varchar(200)
set @sql='select count(1) from tbl ' --计算行数,要显示其他字段,自己列出即可
if @spnumber=''
set @sql=@sql+'group by province '
else
set @sql=@sql+'group by spnumber 'if @cooperatorid!=''
set @sql=@sql+',coorperatorid 'if @begindate!=''
set @sql=@sql+',begindate 'if @enddate!=''
set @sql=@sql+',enddate 'exec(@sql)
go
@spnumber varchar(20),
@cooperatorid varchar(20),
@begindate varchar(20),
@enddate varchar(20)
)
as
declare @sql varchar(200)
set @sql='select count(1) from tbl ' --计算行数,要显示其他字段,自己列出即可
if @spnumber=''
set @sql=@sql+'group by province '
else
set @sql=@sql+'group by spnumber 'if @cooperatorid!=''
set @sql=@sql+',coorperatorid 'if @begindate!=''
set @sql=@sql+',begindate 'if @enddate!=''
set @sql=@sql+',enddate 'exec(@sql)
go
解决方案 »
- 如何给不同的数据库设置不同不同的密码
- 这样的数据结构怎样设计TABLE最优
- MSSql两表查询
- 如何搭建ODBC数据库,静态的,在线等
- 求一sql语句或函数
- 急急急!!!sqlserver连接oracle时的错误,请高手指教!!!
- 奇怪的问题。。。
- SQL数据库出现了连结错误,提示为一般性网络错误,请检查网络文档 connectionopen(preloginhandshake())
- 在客户端通过ODBC连接SQL SERVER7,总提示找不到服务器,为何?(服务器刚装好,是不是要那些设置)由于分不多系统只能给22分,可怜可怜我吧!!
- Interbase有事务日志吗,能不能像SQL Server那样撤消已提交的事务
- 帮我简化下sql吧
- 麻烦您帮我看一下,
@begindate varchar(20),
@enddate varchar(20),
@spnumber varchar(20)=null,
@cooperatorid varchar(20)=null
)
as
declare @sql varchar(200)
set @sql='select count(1) from tbl where dt between @begindate and @enddate '
if @spnumber is not null
set @sql=@sql+'group by spnumber '
else
set @sql=@sql+'group by province 'if @cooperatorid is not null
set @sql=@sql+',coorperatorid 'exec(@sql)
go
如group by spnumber的话,在select中除了count(1)之外,还要选出spnumber在前台显示的
create proc kk(
@spnumber varchar(20),
@cooperatorid varchar(20),
@begindate varchar(20),
@enddate varchar(20)
)
as
declare @sql1 varchar(200),@sql2 varchar(200)
set @sql1='select '
set @sql2=' count(1) from tbl '
if @spnumber=''
begin
set @sql1=@sql1+'province '
set @sql2=@sql2+'group by province '
end
else
begin
set @sql1=@sql1+' spnumber'
set @sql2=@sql2+'group by spnumber '
endif @cooperatorid!=''
begin
set @sql1=@sql1+',coorperatorid'
set @sql2=@sql2+',coorperatorid '
endif @begindate!=''
begin
set @sql1=@sql1+',begindate'
set @sql2=@sql2+',begindate '
endif @enddate!=''
begin
set @sql1=sql1+',enddate '
set @sql2=@sql2+',enddate '
endexec(@sql1+@sql2)
go
@begindate varchar(20),
@enddate varchar(20),
@spnumber varchar(20)=null,
@cooperatorid varchar(20)=null
)
as
declare @sql varchar(8000),@col varchar(8000)
if @spnumber is not null
select @sql=' group by spnumber ',@col=spnumber
else
select @sql=' group by province ',@col=provinceif @cooperatorid is not null
select @sql=@sql+',cooperatorid ',@col=@col+',cooperatorid 'exec('select '+@col+',count(1) from tb where dt between @begindate and @enddate '+@sql )
go
if @cooperatorid<>'' 这样写的 :(
create proc kk(
@spnumber varchar(20),
@cooperatorid varchar(20),
@begindate varchar(20),
@enddate varchar(20)
)
as
declare @sql1 varchar(200),@sql2 varchar(200)
select @sql1='select ', @sql2=' count(1) from tbl '
if @spnumber=''
select @sql1=@sql1+'province ',@sql2=@sql2+'group by province '
else
select @sql1=@sql1+' spnumber',@sql2=@sql2+'group by spnumber 'if @cooperatorid<>''
select @sql1=@sql1+',coorperatorid',@sql2=@sql2+',coorperatorid 'if @begindate<>''
select @sql1=@sql1+',begindate',@sql2=@sql2+',begindate 'if @enddate<>''
select @sql1=sql1+',enddate ', @sql2=@sql2+',enddate 'exec(@sql1+@sql2)
go